Postgres plpgsql with PERFORM data-modifying CTE queries

Viewed 1225

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;
2 Answers
Related