Thread: INSERT ... RETURNING in v8.2
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." 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? -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988]
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/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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