Re: Example of RETURNING clause to get auto-generated keys from INSERT

Let's say you have a table with "id, value" columns.

And your normal query would be this:

INSERT into mytable (id,value) values (1,"foo"),(2,"bar");

Your new query would be like this:

INSERT into mytable (id,value) values (1,"foo"),(2,"bar")
RETURNING id;

And you would get a result back with one column (id) and
two rows (the newly inserted keys).  You can also return
other fields if you like, you're not limited to just the
generated keys.



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ken Johanson
Sent: Tuesday, January 23, 2007 10:50 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Example of RETURNING clause to get auto-generated
keys from INSERT


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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/