Re: Using results from DELETE ... RETURNING

On Fri, Jun 05, 2009 at 07:13:43PM -0400, Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
> > On Fri, Jun 5, 2009 at 6:51 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> >> I think you can loop over the results in plpgsql, for instance
>
> > also sql functions can direct 'returning' results directly to the
> > return of the function (at least in 8.4).
>
> That bit is new in 8.4, which is why I didn't mention it.  But I
> think the plpgsql loop way works further back.

Would it be super-complicated to do this with CTEs for 8.5?  They seem to
have sane properties like getting executed exactly once.

This could look like:

WITH t AS (
    DELETE FROM foo RETURNING * FROM a
)
INSERT INTO foo_audit ...

Cheers,
David (Yes, I know the spec doesn't have row-changing operations in
either part of the above.  Yet ;)
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate