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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ken Johanson Sent: Tuesday, January 23, 2007 10:50 PM To: pgsql-general@xxxxxxxxxxxxxx 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/
