SEO

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/i...ql-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]

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGbq8ZIB7bNG8LQkwRAms+AJ9sS5gORkfFSjGI3XfVVp D3d5jLWQCfcpn+
/xg7z48IkNyGnryUZ4yu0k0=
=5ykN
-----END PGP SIGNATURE-----

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]

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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]

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/i...ql-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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> 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 *)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly