Re: Writeable CTEs and side effects

Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> Is the above form:
>> with x as (delete .. returning *) insert into y select * from x
>> going to be allowed?  I was informed on irc that it wasn't...it would
>> have to be written as:
>> insert into y with x as (delete .. returning *) select * from x
> 
> I would think that we would require the former and forbid the latter.
> One of the basic limitations of the feature is going to be that you
> can only have WITH (something RETURNING) at the top level, and the
> latter syntax doesn't look like that to me.

I'm looking at this, and if I understood correctly, you're suggesting
we'd add a WithClause to InsertStmt.  Would we also allow this?

WITH t1 AS (DELETE FROM foo RETURNING *)
INSERT INTO bar
WITH t2 AS (VALUES(0))
SELECT * FROM t1 UNION ALL
SELECT * FROM t2;

I could also see use for adding this for UDPATE and DELETE too, i.e.

WITH t AS (DELETE FROM foo RETURNING id)
UPDATE bar SET foo_id = NULL FROM t WHERE t.id = bar.foo_id;

Did I misunderstand something here?


Regards,
Marko Tiikkaja