postgresql - 从 plpgsql 中的 FOR 循环切换到基于集合的 SQL 命令

Question

我有一个非常繁重的查询，需要重写 FOR 循环，我想做的更简单，使用更多的 SQL 而不是 plpgsql 结构。查询看起来像:

FOR big_xml IN SELECT unnest(xpath('//TAG1', my_xml)) LOOP
   str_xml = unnest(xpath('/TAG2/TYPE/text()', big_xml));

      FOR single_xml IN SELECT unnest(xpath('/TAG2/single', big_xml)) LOOP

         CASE str_xml::INT
           WHEN 1
           THEN
            INSERT INTO tab1(id, xml) VALUES (1, single_xml);
           WHEN 2
           THEN
            INSERT INTO tab2(id, xml) VALUES (1, single_xml);
           WHEN 3
            [...] 
           WHEN 11
            [...]
           ELSE 
              RAISE EXCEPTION 'something'
           END CASE;
      END LOOP;
    END LOOP;

RETURN xmlelement(NAME "out", xmlforest(1 AS out));

我已经开始重写它以获得更好的性能:

INSERT INTO tab1(id, xml)
  SELECT 1, unnest(xpath('/TAG2/single', (SELECT unnest(xpath('//TAG1', my_xml)))); 

但我不确定如何处理那些 CASE ... INSERT 语句。有任何想法吗？还是我的方法完全错误？

25.02.14 编辑:PostgreSQL 9.3.1

Answer 1

要根据您的数据插入不同的表，您需要一些程序代码。但是您可以使用单个循环和嵌套的 unnest():

DEFINE
   int_xml    int;
   single_xml xml;
BEGIN
   FOR r IN                     -- or you can use two variables
      SELECT xpath('/TAG2/single', big_xml))[1]::text::int AS int_xml
           , unnest(xpath('/TAG2/TYPE/text()', big_xml)) AS single_xml
      FROM  (SELECT unnest(xpath('//TAG1', my_xml)) AS big_xml) sub
   LOOP
      CASE int_xml
      WHEN 1 THEN
         INSERT INTO tab1(id, xml) VALUES (1, single_xml);
      WHEN 2 THEN
         INSERT INTO tab2(id, xml) VALUES (1, single_xml);
      WHEN 3 THEN
          ...

      ELSE 
         RAISE EXCEPTION 'something'
      END CASE;
   END LOOP;
END

或者采用临时表或 CTE 并附加多个 INSERT 语句。对于很多行应该更快。

WITH cte AS (
   SELECT xpath('/TAG2/single', big_xml))[1]::text::int AS int_xml
        , unnest(xpath('/TAG2/TYPE/text()', big_xml)) AS single_xml
   FROM  (SELECT unnest(xpath('//TAG1', my_xml)) AS big_xml) sub
), i1 AS (
   INSERT INTO tab1(id, xml)
   SELECT 1, single_xml
   FROM   cte
   WHERE  int_xml = 1
), i2 AS (
   INSERT INTO tab2(id, xml)
   SELECT 1, single_xml
   FROM   cte
   WHERE  int_xml = 2
),
   ...
)
SELECT int_xml INTO my_var
FROM   cte
WHERE  int_xml <> ALL ({'1','2','3','11'}::int[])
LIMIT  1;

IF FOUND THEN
   RAISE EXCEPTION 'One of the int_xml did not fit: %!', my_var;
END IF;

最后一位补偿了原件中的异常。

如果您的 big_xml 非常大，请确保为 CTE 提供足够的临时缓冲区。如果常规设置太低，请仅为该 session 增加 temp_buffers 设置。此相关答案中的详细信息:
How can I insert common data into a temp table from disparate schemas?