Re: Synthesize support for Statement.getGeneratedKeys()?

On 23-Jan-07, at 2:00 AM, Ken Johanson wrote:

>>>>> My real question is, what about the case where multiple VALUES
>>>>> are inserted; if I have 3 values should I call the sequence 3
>>>>> times? What is the most efficient was to do that? (Can I do it
>>>>> in a single query?)
>>>>
>>>> I don't think you should use "currval" or "nextval" at all. A
>>>> general solution in the JDBC driver should even work in the case
>>>> of triggers that interfere with the value of a sequence. Or
>>>> which might change the value actually inserted into the table.
>>>> Just think of an insert trigger that uses a sequence for a
>>>> second time.
>>>>
>>>> There is only one way to reliably get the database generated
>>>> values: the RETURNING clause.
>>>>
>>>> So my basic suggestion was to rewrite a query written as:
>>>> "INSERT INTO tab VALUES (...)"
>>>> into
>>>> "INSERT INTO tab VALUES (...) RETURNING x"
>>>>
>>>> With x being either (a) what the user specified using the Java
>>>> API (i.e. any column names) or (b) the primary key column(s) (or
>>>> other columns having a "DEFAULT currval(...)").
>>>> The second case (b) I would leave for later, since it requires
>>>> parsing the query and finding the table which will be inserted
>>>> into. And you would have to use database meta data to find the
>>>> columns to return.
>>>>
>
>
> I think that, given everyone's input (including Vit's, thanks) and
> mention of possible variation on query, possible need to parse for
> table/column names, and/or need to call database metadata / or
> result set metadata (to get keys?) (which require another trip to
> the server?)... this might be out of my league. Well, even if I did
> get it working, it likely would not work in every case (triggers
> etc), and would eventually be replaced when V4 protocol comes around.
>
> Unless one of the PG folks can prescribe, in exact terms, the very
> best way to execute this (after which I would build out the actual
> patch)... then I may have to bow out of this (it's complex / error
> prone enough to frighten lil'ol me, and time is a bit short on my
> end too I'm afraid).

If we can implement the one which does specify the keys that would be
useful. Statement.getGeneratedKeys( columns )
>
> Perhaps it's better for everyone if we lobby to have the backend/
> protocol to add this natively (as you all have suggested). So..
>
> Does anyone know if the actual server core natively has the ability
> to build created-keys resultsets (without having to modify the
> query / RETURNS), or is this truly a protocl bottleneck?...
>
I don't know for absolute certainty but I highly suspect that it
does, since it can return the columns returned via insert returning

Dave
> Thanks,
> Ken
>
>
>