Re: JDBC 3.0 support? - Mailing list pgsql-jdbc

Tom,

This essentially would rely on the 'returning' functionality that has
been discussed previously on hackers.  Specifically the spec states that
the database should return all 'auto generated keys' or specifically
requested columns for inserted rows.  However the proposals I have seen
for 'returning' are done through a returning sql clause.  This would
mean the jdbc driver would need to parse the user supplied sql and
insert the correct returning clause into the proper location in the sql
statement.  It also means that the driver would need a way to determine
which columns were 'auto generated' (which shouldn't be too difficult
for serial columns, but if the auto-generating mechanism was a trigger
or a default clause, I am not sure how that would be done).

Here is what the spec
(http://java.sun.com/products/jdbc/download.html#corespec30) says:

13.6 Retrieving Auto Generated Keys

Many database systems have a mechanism that automatically generates a
unique key field when a row is inserted. The method
Statement.getGeneratedKeys, which can be called to retrieve the value of
such a key, returns a ResultSet object with a column for each
automatically generated key. A flag indicating that any auto generated
columns should be returned is passed to the methods execute,
executeUpdate or prepareStatement when the statement is executed or
prepared.

Additional methods allow the ordinals or names of the columns that
should be returned to be specified.

Calling ResultSet.getMetaData on the ResultSet object returned by
getGeneratedKeys will produce a ResultSetMetaData object that can be
used to determine the number, type and properties of the generated keys.

In some cases, such as in an insert select statement, more than one key
may be returned. The ResultSet object returned by getGeneratedKeys will
contain a row for each key that a statement generated. If no keys are
generated, an empty result set will be returned.

The concurrency of the ResultSet object returned by getGeneratedKeys
must be CONCUR_READ_ONLY. The type of the ResultSet object must be
either TYPE_FORWARD_ONLY or TYPE_SCROLL_INSENSITIVE.

The method DatabaseMetaData.supportsGetGeneratedKeys returns true if a
JDBC driver and underlying data source support the retrieval of
automatically generated keys.


thanks,
--Barry



Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
>
>>The driver is already 3.0 compliant for that.  It correctly returns
>>false to DatabaseMetaData.supportsGetGeneratedKeys() indicating that
>>postgresql does not support this functionality.  This isn't likely to
>>change unless some significant new functionality is introduced in the
>>server to do this.
>
>
> What exactly would the server need to do to support this?  Do you think
> the effort is warranted?
>
>             regards, tom lane
>