>>>> 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).
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?...
Thanks,
Ken