Thread: Using results from DELETE ... RETURNING

Hi all,

When RETURNING from a DELETE statement (or similar), how do you access the
results being returned?

Something like:

SELECT COUNT(*) FROM (DELETE FROM a RETURNING *) ;

sounds reasonable but results in a syntax error. I am able to return single
results into a variable or record, but not more than one result.

I could use a SELECT ... FOR UPDATE in the meantime (and then a DELETE), I
just wanted to know what the use of RETURNING * was if you can't do anything
with the results.

Thanks!

Shak

On Mon, Jun 01, 2009 at 04:21:27PM +0100, Shak wrote:
> When RETURNING from a DELETE statement (or similar), how do you access the
> results being returned?
>
> Something like:
>
> SELECT COUNT(*) FROM (DELETE FROM a RETURNING *) ;
>
> sounds reasonable but results in a syntax error. I am able to return single
> results into a variable or record, but not more than one result.

You can't. It's on the TODO list (http://wiki.postgresql.org/wiki/Todo). Look
for "Allow INSERT/UPDATE ... RETURNING" and "Allow INSERT ... DELETE ... RETURNING"

- Josh / eggyknap

Joshua Tolley <eggyknap@gmail.com> writes:
> On Mon, Jun 01, 2009 at 04:21:27PM +0100, Shak wrote:
>> Something like:
>> SELECT COUNT(*) FROM (DELETE FROM a RETURNING *) ;
>> sounds reasonable but results in a syntax error. I am able to return single
>> results into a variable or record, but not more than one result.

> You can't. It's on the TODO list (http://wiki.postgresql.org/wiki/Todo).

I think you can loop over the results in plpgsql, for instance

    for rec in DELETE FROM a RETURNING * loop
        ... do something with rec ...
    end loop;

            regards, tom lane

On Fri, Jun 5, 2009 at 6:51 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Joshua Tolley <eggyknap@gmail.com> writes:
>> On Mon, Jun 01, 2009 at 04:21:27PM +0100, Shak wrote:
>>> Something like:
>>> SELECT COUNT(*) FROM (DELETE FROM a RETURNING *) ;
>>> sounds reasonable but results in a syntax error. I am able to return single
>>> results into a variable or record, but not more than one result.
>
>> You can't. It's on the TODO list (http://wiki.postgresql.org/wiki/Todo).
>
> I think you can loop over the results in plpgsql, for instance
>
>        for rec in DELETE FROM a RETURNING * loop
>                ... do something with rec ...
>        end loop;
>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


also sql functions can direct 'returning' results directly to the
return of the function (at least in 8.4).

merlin

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.

            regards, tom lane

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

David Fetter <david@fetter.org> writes:
> Would it be super-complicated to do this with CTEs for 8.5?  They seem to
> have sane properties like getting executed exactly once.

Hmm, interesting thought.  The knock against doing RETURNING as an
ordinary subquery is exactly that you can't disentangle it very well
from the upper query (and thus, it's hard to figure out when to fire
triggers, to take just one problem).  But we've defined CTEs much more
restrictively, so maybe the problems can be solved in that context.

            regards, tom lane

On Sun, Jun 7, 2009 at 12:29 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> David Fetter <david@fetter.org> writes:
>> Would it be super-complicated to do this with CTEs for 8.5?  They seem to
>> have sane properties like getting executed exactly once.
>
> Hmm, interesting thought.  The knock against doing RETURNING as an
> ordinary subquery is exactly that you can't disentangle it very well
> from the upper query (and thus, it's hard to figure out when to fire
> triggers, to take just one problem).  But we've defined CTEs much more
> restrictively, so maybe the problems can be solved in that context.
>

being able to do this would probably give 'best of class' approach to
dealing with update/insert rules to views that want to work
w/returning clause (although, still a complete mess), plus numerous
other useful things.

merlin