具有PERFORM数据修改CTE查询的Postgres plpgsql [英] Postgres plpgsql with PERFORM data-modifying CTE queries
问题描述
我尝试在下面的代码示例中模拟我的问题.在下面的代码中,我在过程中执行delete from test2
.效果很好:
I tried to simulate my problem in the code example below. In the code below, I am doing a delete from test2
in a procedure. This works great:
但是,就我而言,此delete
是相当复杂的CTE的一部分,具有多个更新和插入(没有选择,因此我将虚拟select 1
作为主查询添加).让我们对此进行模拟:
However, in my case, this delete
is part of a rather complex CTE with several updates and inserts (there are no selects so I add a dummy select 1
as main query). Let's simulate this as this:
with my_cte as(delete from test2) select 1
现在,我们知道,我们必须使用perform
关键字来执行此操作:
Now, as we know, we have to use the perform
keyword to execute this:
perform (with my_cte as(delete from test2) select 1);
我遇到以下错误:
ERROR: WITH clause containing a data-modifying statement must be at the top level
这是plpgsql的限制吗?
Is this a limitation of plpgsql?
(请注意,这只是一个解释我的问题的示例.我知道查询实际上没有任何意义.)
(Please note that this is just an example to explain my problem. I know the queries do not really make any sense.)
create table test
(
key int primary key
);
create table test2
(
key int primary key
);
create function test() returns trigger as
$$
begin
raise notice 'hello there';
-- this does work
delete from test2;
-- this doesn't work
perform (with my_cte as(delete from test2) select 1);
return new;
end;
$$
language plpgsql;
create trigger test after insert on test for each row execute procedure test();
insert into test(key) select 1;
推荐答案
您可以使用CTE组合多个返回查询的DELETE,INSERT,UPDATE.而且您不需要执行它,例如:
You can use CTE for combining several DELETE, INSERT, UPDATE returning queries. And you dont need perform for it, eg:
t=# begin; do $$ begin with d as (delete from s133 returning *) insert into s133 select * from d; raise info '%',(select count(1) from s133);
end; $$; commit;
BEGIN
Time: 0.135 ms
INFO: 4
DO
Time: 0.469 ms
COMMIT
Time: 0.887 ms
t=# select count(1) from s133;
count
-------
4
(1 row)
在这里我删除四行,并在CTE中将它们重新插入
here I delete four rows and in CTE insert them back
这篇关于具有PERFORM数据修改CTE查询的Postgres plpgsql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!