getGeneratedKeys

Hello,

This is just a request in the hope it may help not all but 95% of devs. The
other 5% would know by notice that they have to write their own solution; for
now, 100% of devs have to write their own.

I have followed
http://archives.postgresql.org//pgsql-jdbc/2004-09/msg00191.php
and
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&safe=off&threadm=Pine(dot)LNX(dot)4(dot)33(dot)0312301523060(dot)5331-100000(at)leary(dot)csoft(dot)net&rnum=3&prev=/groups?q=generated+keys&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.interfaces.jdbc&c2coff=1&safe=off&selm=Pine.LNX.4.33.0312301523060.5331-100000%40leary.csoft.net&rnum=3
and I understand quite well that currval is useless with multiple inserted
rows, and the one inserted row situation with two colums having the same
sequence as default values cannot rely on currval. I consider these situations
to be in those 5% or less where the devs know what they are doing.

Sequences appended to columns as defaults can easily be retrieved

"SELECT column_default FROM information_schema.columns WHERE table_name = '" +
tblName + "' AND column_default LIKE 'nextval(''%_%_seq''::regclass)'"

The driver may parse the results to get the sequence(s) name(s) and hence
querying again to retrieve the currval(s), returning a resultset with most
often one column and more if there are more than one column with a sequence as
default value. If the same same sequence is found more than one time as
default value, then the driver may generate an error as 'not implemented'.

Of course multiple inserted rows cannot be managed at all.

Concerning having more than one same sequence as default values in a table is
quite an odd situation. Serious devs won't do that.

A table could yet have more than column with a sequence as default, and the
returned generated keys could all be forwarded by the driver if the sequences
are all different.

So I think the driver may provide getGeneratedKeys in a realistic approach,
and generating errors for these very rare odd situations.

Thank you for considering.

On Mon, 9 Mar 2009, Saleem EDAH-TALLY wrote:

> This is just a request in the hope it may help not all but 95% of devs. The
> other 5% would know by notice that they have to write their own solution; for
> now, 100% of devs have to write their own.
>
> I have followed
> http://archives.postgresql.org//pgsql-jdbc/2004-09/msg00191.php
> and
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&safe=off&threadm=Pine(dot)LNX(dot)4(dot)33(dot)0312301523060(dot)5331-100000(at)leary(dot)csoft(dot)net&rnum=3&prev=/groups?q=generated+keys&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.interfaces.jdbc&c2coff=1&safe=off&selm=Pine.LNX.4.33.0312301523060.5331-100000%40leary.csoft.net&rnum=3

Those are pretty old, see:

http://archives.postgresql.org/pgsql-jdbc/2008-11/msg00041.php

http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00015.php

The upcoming 8.4 driver will contain support for getGeneratedKeys. If you
want to test this out yourself, download the code from CVS or a prebuilt
jar from the link in the second message.

Kris Jurka

I've tried for single and multiple inserts and it does work.
I don't know what would be returned if a table has more than one sequence as
default value for columns, I don't have need for this so I won't try.

Just for curiosity, does it work by driver implementation solely or are there
changes in PostgreSQL backend that help the implementation ?

Thanks.

>
> Those are pretty old, see:
>
> http://archives.postgresql.org/pgsql-jdbc/2008-11/msg00041.php
>
> http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00015.php
>
> The upcoming 8.4 driver will contain support for getGeneratedKeys. If you
> want to test this out yourself, download the code from CVS or a prebuilt
> jar from the link in the second message.
>
> Kris Jurka

On Mon, 9 Mar 2009, Saleem EDAH-TALLY wrote:

> I've tried for single and multiple inserts and it does work.
> I don't know what would be returned if a table has more than one sequence as
> default value for columns, I don't have need for this so I won't try.
>
> Just for curiosity, does it work by driver implementation solely or are there
> changes in PostgreSQL backend that help the implementation ?

The 8.2 server release included support for the RETURNING clause for
inserts, updates, and deletes which allow the server to return the values
generated.

http://www.postgresql.org/docs/8.3/static/sql-insert.html

Kris Jurka