Postgres plpgsql with PERFORM data-modifying CTE queries

Question

我试图在下面的代码示例中模拟我的问题。在下面的代码中，我正在一个过程中做一个delete from test2。这很好用：

但是，就我而言，此delete是具有多个更新和插入的相当复杂的 CTE 的一部分(没有选择，所以我添加一个虚拟select 1作为主查询)。让我们模拟一下：

with my_cte as(delete from test2) select 1

现在，正如我们所知，我们必须使用perform关键字来执行它：

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 的限制吗？

(请注意，这只是解释我的问题的一个例子。我知道这些查询实际上没有任何意义。

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;

Answer 1

您可以使用 CTE 组合多个删除、插入、更新返回的查询。而且你不需要为此执行，例如：

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 中将它们重新插入

Answer 2

正如您发现的那样，您既不能在子选择中嵌套这样的WITH子句，也不能这样做

WITH cte AS (...)
PERFORM 1;

一种解决方案是使用SELECT ... INTO dummy而不是PERFORM并忽略结果。

但我不明白为什么你不能用几个 SQL 语句对函数中的DELETEs、UPDATEs 和INSERTs 进行编码，而不是将它们捆绑到 CTE 中。

如果尝试保护自己免受并发数据修改，请使用REPEATABLE READ事务，以便所有语句在数据库的同一快照上运行。