> but it works > > postgres=# do $$begin with x as (select 10) insert into omega select * from x; end;$$; > DO
But this does not:
david=# DO $$ david$# BEGIN
david$# PERFORM * FROM ( david$# WITH inserted AS ( david$# INSERT INTO foo values (1) RETURNING id david$# ) SELECT inserted.id david$# ) x; david$# END; david$# $$; ERROR: WITH clause containing a data-modifying statement must be at the top level LINE 2: WITH inserted AS ( ^ QUERY: SELECT * FROM ( WITH inserted AS ( INSERT INTO foo values (1) RETURNING id ) SELECT inserted.id ) x CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM
yes, in this context you should not use a PERFORM
PL/pgSQL protect you before useless queries - so you can use a CTE without returned result directly or CTE with result via PERFORM statement (and in this case it must be unmodifing CTE).
Sorry, I don't see any problem - why you return some from CTE and then you throw this result?