Postgres plpgsql with PERFORM data-modifying CTE q

问题:

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:

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

Now, as we know, we have to use the perform keyword to execute this:

perform (with my_cte as(delete from test2) select 1);

I am getting the following error:

ERROR: WITH clause containing a data-modifying statement must be at the top level

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;

回答1:

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)

here I delete four rows and in CTE insert them back

回答2:

As you found out, you can neither nest such a WITH clause in a subselect, not can you do

WITH cte AS (...)
PERFORM 1;

One solution would be to use SELECT ... INTO dummy instead of PERFORM and ignore the result.

But I don't see why you cannot code the DELETEs, UPDATEs and INSERTs in your function with several SQL statements rather than bundling them into CTEs.

If you try to protect yourself from concurrent data modification, use a REPEATABLE READ transaction so that all your statements operate on the same snapshot of the database.