Greetings,
I am looking into possibly contributing some code for one of the
existing PG drivers, that will allow us to, after INSERT, get a
ResultSet containing the server generated keys (sequences or other).
I've been told that (short of implementing a new V4 server protocol) the
most effective way to do this, may be to use PG's RETURNING clause.
However I could really use some example queries, since I'm not
proficient enough with PG and this clause to know how to get the values.
I do know that the query should:
-support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4),
should return a result with 2 rows containing the new keys (one for each
column the users declares).
-query the values atomically (so that insert by another client won't
skew the curval / sequence) (obvious but deserves mention)
-ideally be predictable - just in case the sequence doesn't use a
increment value of one, or if some other non-sequence (triggers) or
numeric (uuids) generator is used.
-ideally not require parsing the user INSERT query (for table names
etc), though I expect that (in order to use RETURNING) I will have to
append to it.
The API I'd implement this for (jdbc), does require us to declare what
columns we are interested in getting generated keys for, so that might
preclude needing resultset metadata to know which columns have server
generated keys.
So if anyone can give SQL samples of how to best make this work, I would
be very much appreciative.
Thanks,
Ken