INSERT ... RETURNING in v8.2

On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:

Well, at least on v8.2.4 I cannot return count(*), that is the
number of lines actually inserted into the table. Nor I can return
any aggregate function of them.

I don't think anybody considered the possibility of using an aggregate
there, primary because for an aggregate you need a group by. What has
been discussed is nested statements, like:

SELECT a, count(*) FROM
(INSERT <foo> RETURNING a, b)
GROUP BY a;

But I don't think that's implemented (the interactions with triggers
havn't been worked out I think)

Amk I doing anything wrong or is there some missing sentence in the
documentation?

When the docs talk about an "expression" they don't mean aggregates,
since they are not functions in the ordinary sense.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Tuesday 12 June 2007 16:35:05 Martijn van Oosterhout wrote:

On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:

Well, at least on v8.2.4 I cannot return count(*), that is the
number of lines actually inserted into the table. Nor I can
return any aggregate function of them.

I don't think anybody considered the possibility of using an
aggregate there, primary because for an aggregate you need a group
by. What has been discussed is nested statements, like:

SELECT a, count(*) FROM
(INSERT <foo> RETURNING a, b)
GROUP BY a;

But I don't think that's implemented (the interactions with
triggers havn't been worked out I think)

Amk I doing anything wrong or is there some missing sentence in
the documentation?

When the docs talk about an "expression" they don't mean
aggregates, since they are not functions in the ordinary sense.

Hope this helps,

I feel that your remarks make some sense.

First, the documentation says "any expression using the table's
columns is allowed".

Second, I'm not using nested statements, but rather a plain
INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
function body). It should not need any GROUP BY as the query is
plain.

Maybe the solution is somewhere in between what you say and what I'd
expect. Of course at the moment I have added an extra SELECT COUNT(*)
in order to get that number.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

Vincenzo Romano <vincenzo.romano@gmail.com> writes:

Well, at least on v8.2.4 I cannot return count(*), that is the
number of lines actually inserted into the table. Nor I can return
any aggregate function of them.
Am I doing anything wrong or is there some missing sentence in the
documentation?

I would think the error message you get would make it pretty plain
that this wasn't just an oversight:

regression=# insert into int4_tbl default values returning count(*);
ERROR: cannot use aggregate function in RETURNING

RETURNING is supposed to return one row per inserted/deleted/updated
tuple, so what you suggest isn't sensible.

regards, tom lane

Vincenzo Romano <vincenzo.romano@gmail.com> writes:

Second, I'm not using nested statements, but rather a plain
INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
function body). It should not need any GROUP BY as the query is
plain.

Maybe the solution is somewhere in between what you say and what I'd
expect. Of course at the moment I have added an extra SELECT COUNT(*)
in order to get that number.

Umm ... doesn't
GET DIAGNOSTICS integer_var = ROW_COUNT;
do what you want?

regards, tom lane

On Tuesday 12 June 2007 18:26:35 Tom Lane wrote:

Vincenzo Romano <vincenzo.romano@gmail.com> writes:

Second, I'm not using nested statements, but rather a plain
INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
function body). It should not need any GROUP BY as the query is
plain.

Maybe the solution is somewhere in between what you say and what
I'd expect. Of course at the moment I have added an extra SELECT
COUNT(*) in order to get that number.

Umm ... doesn't
GET DIAGNOSTICS integer_var = ROW_COUNT;
do what you want?

regards, tom lane

This's a real good point, as well as the previous one.
Thanks again, Tom.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

On Jun 12, 2007, at 10:18 AM, Vincenzo Romano wrote:

Hi all.
I'm trying to use this wonderful feature (thanks to anyone who
suggested/committed/implemented it).

According to the documentation:
(http://www.postgresql.org/docs/8.2/interactive/sql-insert.html)

"The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily
useful for obtaining values that were supplied by defaults, such
as a serial sequence number. However, any expression using the
table's columns is allowed. The syntax of the RETURNING list is
identical to that of the output list of SELECT."

Holy Crud!
you mean to tell me I can replace:

insert into table(string) values(('one'),('two'),('three'));
select idx from table where string in ('one','two','three');

with

insert into table(string) values(('one'),('two'),('three')) returning
idx;

?????

I realize that this is an extension to standard SQL but it sure would
save me a lot.

I'm wondering just how many other things I'm missing....
(I am really starting to like this database more every week)

On Jun 12, 2007, at 11:40 AM, Vincenzo Romano wrote:

On Tuesday 12 June 2007 16:35:05 Martijn van Oosterhout wrote:

On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:

Well, at least on v8.2.4 I cannot return count(*), that is the
number of lines actually inserted into the table. Nor I can
return any aggregate function of them.

I don't think anybody considered the possibility of using an
aggregate there, primary because for an aggregate you need a group
by. What has been discussed is nested statements, like:

SELECT a, count(*) FROM
(INSERT <foo> RETURNING a, b)
GROUP BY a;

But I don't think that's implemented (the interactions with
triggers havn't been worked out I think)

Amk I doing anything wrong or is there some missing sentence in
the documentation?

When the docs talk about an "expression" they don't mean
aggregates, since they are not functions in the ordinary sense.

Hope this helps,

I feel that your remarks make some sense.

First, the documentation says "any expression using the table's
columns is allowed".

Second, I'm not using nested statements, but rather a plain
INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
function body). It should not need any GROUP BY as the query is
plain.

Maybe the solution is somewhere in between what you say and what I'd
expect. Of course at the moment I have added an extra SELECT COUNT(*)
in order to get that number.

Not entirely sure what you're doing but at least with Perl you can
always ask for the number of affected rows: $sth->rows after you run
an INSERT.

Holy Crud!
you mean to tell me I can replace:

insert into table(string) values(('one'),('two'),('three'));
select idx from table where string in ('one','two','three');

Yes.

A smart ORM library should, when you create a new database object from
form values, use INSERT RETURNING to grab all the default values (SERIALs,
DEFAULTs, trigger-generated stuff etc). This is much more elegant than
digging to find the sequence name to currval() it !

I think this feature is priceless (but it would be even better if I could
do INSERT INTO archive (DELETE FROM active WHERE blah RETURNING *)

Tom Allison escribi�:

Holy Crud!
you mean to tell me I can replace:

insert into table(string) values(('one'),('two'),('three'));
select idx from table where string in ('one','two','three');

with

insert into table(string) values(('one'),('two'),('three')) returning
idx;

?????

I realize that this is an extension to standard SQL but it sure would
save me a lot.

You are wrong -- you can do it, but it is not an extension. It is in
the standard.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Allison escribi�:

insert into table(string) values(('one'),('two'),('three')) returning
idx;

I realize that this is an extension to standard SQL but it sure would
save me a lot.

You are wrong -- you can do it, but it is not an extension. It is in
the standard.

Uh, I don't even see RETURNING as a reserved word in SQL2003.

regards, tom lane