8.2: select from an INSERT returning?

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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
> that possible?

No.

regards, tom lane

On Wed, Sep 20, 2006 at 01:42:59PM -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter <david(at)fetter(dot)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

On Wed, 2006-09-20 at 14:08 -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)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