从 plpgsql 中的 FOR 循环切换到基于集合的 SQL 命令 [英] Switching from FOR loops in plpgsql to set-based SQL commands

问题描述

我有相当繁重的查询需要重写 FOR 循环，并希望使用更多 SQL 而不是 plpgsql 结构来简化它.查询如下:

I've got quite heavy query with FOR loop to rewrite and would like to do it simpler, using more SQL instead of plpgsql constructions. The query looks like:

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));

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

I've started to rewrite it for better performance with:

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

但我不确定如何处理那些 CASE ... INSERT 语句.有任何想法吗?或者也许我的方法完全错误?

But I'm not sure how to deal with those CASE ... INSERT statements. Any ideas? Or maybe my approach is completely wrong?

25.02.14 PostgreSQL 9.3.1

edited 25.02.14: PostgreSQL 9.3.1

推荐答案

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

To insert into different tables, depending on your data, you need some procedural code. But you can make do with a single loop and a nested 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 语句.对于很多行应该更快.

Or take a temporary table or CTE and append multiple INSERT statements. Should be faster for lots of rows.

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;

最后一点弥补了原始文件中的异常.

The last bit compensates for the exception in your original.

如果您的 big_xml 真的很大，请确保为 CTE 提供足够的临时缓冲区.如果常规设置太低，请仅为该会话增加 temp_buffers 设置.此相关答案中的详细信息:
怎么能我将公共数据从不同的模式插入到临时表中?

If your big_xml is real big, make sure you provide enough temporary buffers for the CTE. If the general setting is too low, increase the temp_buffers setting for this session only. Details in this related answer:
How can I insert common data into a temp table from disparate schemas?

这篇关于从 plpgsql 中的 FOR 循环切换到基于集合的 SQL 命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！