Re: 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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joshua Tolley <eggyknap(at)gmail(dot)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(at)postgresql(dot)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(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.

regards, tom lane

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

David Fetter <david(at)fetter(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Fetter <david(at)fetter(dot)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

On Sun, Jun 07, 2009 at 12:29:56AM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)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.

I was discussing this with Andrew Gierth in IRC, who thought that
putting RETURNING inside the WITH clause would be relatively easy, at
least for the parser and planner. For the executor, he suggested that
one approach might be to make INSERT, UPDATE and DELETE into their own
nodes.

Comments?

Cheers,
David.
--
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

David Fetter wrote:
> I was discussing this with Andrew Gierth in IRC, who thought that
> putting RETURNING inside the WITH clause would be relatively easy, at
> least for the parser and planner. For the executor, he suggested that
> one approach might be to make INSERT, UPDATE and DELETE into their own
> nodes.

David asked me to post his (and mine) experimental work in progress
patch for this here. The patch in the current state does not work. It
dies in executor on:
ERROR: attribute 1 has wrong type
DETAIL: Table has type tid, but query expects integer.
Since I know nothing about postgres' executor I am only guessing it
thinks the query is SELECT instead of DELETE RETURNING.
Also I think those query->commandType == CMD_SELECT ? query->targetList
: query->returningList in several places might not be the right way to go.
Anyway it's beginning and maybe somebody who knows what he is doing
could help or continue the work.

--
Regards
Petr Jelinek (PJMODOS)

On Sun, Jun 14, 2009 at 05:59:58PM +0200, Petr Jelinek wrote:
> David Fetter wrote:
>> I was discussing this with Andrew Gierth in IRC, who thought that
>> putting RETURNING inside the WITH clause would be relatively easy, at
>> least for the parser and planner. For the executor, he suggested that
>> one approach might be to make INSERT, UPDATE and DELETE into their own
>> nodes.
>
> David asked me to post his (and mine) experimental work in progress
> patch for this here. The patch in the current state does not work. It
> dies in executor on:
> ERROR: attribute 1 has wrong type
> DETAIL: Table has type tid, but query expects integer.
> Since I know nothing about postgres' executor I am only guessing it
> thinks the query is SELECT instead of DELETE RETURNING.
> Also I think those query->commandType == CMD_SELECT ? query->targetList
> : query->returningList in several places might not be the right way to
> go.

I went another way in the attached patch, and thanks :)

> Anyway it's beginning and maybe somebody who knows what he is doing
> could help or continue the work.

This patch fails regression tests and hangs or crashes when attempting
to do a writeable CTE.

Any help getting it into better shape would be greatly appreciated :)

Cheers,
David.
--
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