从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屋!