Re: Using results from DELETE ... RETURNING

On Fri, Jun 05, 2009 at 07:13:43PM -0400, Tom Lane wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> > On Fri, Jun 5, 2009 at 6:51 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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