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

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp@tcd.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