Re: Using results from DELETE ... RETURNING

From: David Fetter <david(at)fetter(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Joshua Tolley <eggyknap(at)gmail(dot)com>, Shak <sshaikh(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Using results from DELETE ... RETURNING
Date: 2009-06-07 04:25:17
Message-ID: 20090607042517.GS5929@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-06-07 04:29:56 Re: Using results from DELETE ... RETURNING
Previous Message Martin Gainty 2009-06-06 22:03:39 Re: PostgreSQL and XA Distributed Transaction Protocol

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-06-07 04:29:56 Re: Using results from DELETE ... RETURNING
Previous Message Tom Lane 2009-06-07 04:11:44 Re: pg_migrator issue with contrib