Re: RETURNING, CTEs and TRANSACTION ISOLATION levels...

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp(at)tcd(dot)ie> writes:
> I was trying to do this:

> DELETE FROM t
> WHERE id IN
> INSERT INTO t_archiv
> (
> SELECT *
> FROM t
> WHERE EXTRACT(EPOCH FROM NOW()) - epok > 15613200
> )
> RETURNING id;

Try putting the INSERT ... RETURNING in a CTE (WITH clause).
We don't support putting RETURNING into any random place
where a sub-select can appear, because the semantics would
be too unpredictable. But a WITH has guaranteed evaluate-once
semantics, so DML with RETURNING is OK there.

(Maybe something more specific than a syntax error would
be nice ...)

regards, tom lane