Postgres Pro
Downloads
Home   >   mailing lists

Thread: 8.2: select from an INSERT returning?

8.2: select from an INSERT returning?

From
Jeff Davis
Date:
I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
that possible?

jdavis=# create table foo(i int);
CREATE TABLE
jdavis=# insert into foo(i) values(1) returning i;
 i
---
 1
(1 row)

INSERT 0 1
jdavis=# select * from (insert into foo(i) values(1) returning i) t;
ERROR:  syntax error at or near "into"
LINE 1: select * from (insert into foo(i) values(1) returning i) t;
                              ^

If not, is there a reason it shouldn't be allowed, or is that a possible
feature for 8.3?

Also, why no GROUP BY or aggregate functions?

I was interested in using the RETURNING clause in place of using
PQcmdTuples() to get information about what was inserted. I don't think
there's any way for a function to modify what is returned by
PQcmdTuples, right?

Regards,
    Jeff Davis

Re: 8.2: select from an INSERT returning?

From
Tom Lane
Date:
Jeff Davis <pgsql@j-davis.com> writes:
> I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
> that possible?

No.

            regards, tom lane

Re: 8.2: select from an INSERT returning?

From
David Fetter
Date:
On Wed, Sep 20, 2006 at 01:42:59PM -0400, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
> > that possible?
>
> No.

What would be involved in making INSERT/UPDATE/DELETE ... RETURNING be
on the same level as other table-like things such as VALUES (...),
..., (...)?

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: 8.2: select from an INSERT returning?

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> What would be involved in making INSERT/UPDATE/DELETE ... RETURNING be
> on the same level as other table-like things such as VALUES (...),
> ..., (...)?

Getting rid of their side-effects, which of course ain't happening.

The problem is the surrounding query might try to execute the command
multiple times ... or not at all ... and what would you like that to
mean?

            regards, tom lane

Re: 8.2: select from an INSERT returning?

From
Jeff Davis
Date:
On Wed, 2006-09-20 at 14:08 -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > What would be involved in making INSERT/UPDATE/DELETE ... RETURNING be
> > on the same level as other table-like things such as VALUES (...),
> > ..., (...)?
>
> Getting rid of their side-effects, which of course ain't happening.
>
> The problem is the surrounding query might try to execute the command
> multiple times ... or not at all ... and what would you like that to
> mean?
>

Wouldn't that be the same as a volatile set-returning function? As I
understand it, 8.2 introduced a feature to prevent a volatile function
from being executed more times than it is listed in the query.

Regards,
    Jeff Davis