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

Ken Johanson wrote:

> -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.

This sample does most of what you want:

alvherre=# create table bar (a serial, b text);
NOTICE: CREATE TABLE will create implicit sequence "bar_a_seq" for serial column "bar.a"
CREATE TABLE
alvherre=# insert into bar (b) values ('hello'), ('world') returning a;
a
---
1
2
(2 filas)

INSERT 0 2

As you predicted, you need to know the column names of the key, which
you can obtain by peeking the system catalogs. That is, unless you use
a "returning *", but then it'll give you all columns and you'll have to
figure out which ones are part of the key anyway. Of course, with
multiple column keys it gets a bit more complex, but it's not really
rocket science.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.