Re: Synthesize support for Statement.getGeneratedKeys()?

>> If someone has pointers to where the augmentaion code should be placed
>> (org.tgresql.jdbc3.Jdbc3ResultSetMetadata I presume), and also the
>> protocol (separate query to the server?, or append RETURNING clause to
>> the DML?).
>
> Right now, the server protocol does not have a separate facility for the
> RETURNING functionality. Therefore you have to append a RETURNING clause
> to the query string.
>
>> Also I cant remember - is it true that when inserting multuple VALUES,
>> that only the first (or last) SEQUENCE can be retrived, or can I
>> populate a resultset using the RETURNING data from by the server?
>
> I suppose it should work with multiple VALUES, but you can simple try. ;-)
>

For the PG & SQL masters out there, I am brain storming this...

1) It seems implicit that I will have to scan the input query for an
existing RETURNING clause, and replace it if it exists; is there any
pre-built query parsing helpers that would help with this? Or for
efficiency I would need to do an indexOf that scans from the end of the
CharSequence... thoughts?

2) Any queries where simply appending a RETURNING can somehow spoof
functionality (UPDATEs, etc), or cause unintended results? Is it always
permissible for RETURNING to be last? (i.e can it appear after other
user clauses, LIMIT, etc)

3) Is there a) an efficient RETURNING clause to pre-populate the
generated-keys result set, or b) should I synthesize it.

4) If 3b is required, then besides incrementing (by one) sequences, any
suggestions on how to correctly synthesize other increment values? Other
key types (OIDs, etc?)

5) To be absolutely sure, inserting multiple values then getting the
sequence back (RETURNING) will happen atomically in the server, correct?
(to avoid getting another transaction's keys).

If someone wants to write up a spec on how this should work then I'll
try to implement it in code. I can work without a spec but I'll make
more incorrect assumptions (not being PG proficient yet).

Thanks,
Ken