Re: Synthesize support for Statement.getGeneratedKeys()?

Ken Johanson wrote:
> Hello,
>
> I've just come to realize that Statement.getGeneratedKeys() is not
> supported in the current PG and/or JDBC driver. Does someone know if
> this is a limitation of PG, or its protocol, or just not yet implemented
> in the JDBC driver? I'm just wondering where, if at all (if I have
> enough brain cells that is :), I could try to offer a patch or ideas..

It would be possible to implement that using the RETURNING clause
supported in recent versions of the server.

See here:
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00035.php
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00037.php

Unfortunately, all my available spare time went into implementing
support for standards_conforming_strings in the 8.2 release cycle.

> Then question 2; I did see a discussion where it was suggested that we
> could get roughly the same effect by issuing a
> SELECT currval('<sequence-name>'); after the DML...
> http://archives.postgresql.org/pgsql-jdbc/2005-10/msg00035.php
> Would it then be feasible internal to the JDBC driver to (create an
> option that would enable) always implicitly append that query to the
> (String)sql arg of Statment.executeUpdate(String sql, String[]
> columnNames)? I mean, just internally attempt to create the same
> behavior as what this method should be doing?

As was discussed before, a solution just for sequences is not general
enough, therefore the RETURNING thing.

> Question 3 is, it seems like option two would only return the last
> created key, not set of keys, in the case where multiple rows were
> inserted.. is this accurate?

Seems correct. And that's one of the objections to the idea.

> Unfortunately if I cant find a way to make my target-app work with PG
> (without adding PG-specific modifications for getting keys), I'm
> probably not going to be able to make the switch to PG unfortunately -
> the code I'm working with makes really, really extensive use of
> retrieved keys..

Could you comment on my mail above (the second one) -- what API-methods
does your software use, actually?

Best Regards
Michael Paesold