Thread: Getting value of SERIAL column after insert from libpq?

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hi,
>
> I have written a C program to insert a row into a table with a
> SERIAL column.
>
> Is there a way of returning the inserted value for this column
> to my program? I.e. if there are rows with the serial column
> for 1,2,3,4 and 5, and I insert a row, my program needs to be
> told "6" for the new serial. There may be many instances of the
> program running simultaneously so I can't do a "select max..."
> or "select last_value..." workaround because by the time the
> select is done, there may have been other rows inserted so the
> last_value would be wrong. Also the program needs to be table-name
> and column-name independent so that it can work for ANY insert
> query into a table with a SERIAL column.

Answer is that currval('seqence_name') will return your last sequence
number, even if another session has assigned a sequence number since
your nextval() call.

---------------------------------------------------------------------------


test=> create table oo(x serial, y text);
NOTICE:  CREATE TABLE will create implicit sequence 'oo_x_seq' for
SERIAL column 'oo.x'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'oo_x_key' for
table 'oo'
CREATE
test=> insert into oo (y ) values ('ds');
INSERT 18879 1
test=> \d oo
                          Table "oo"
 Attribute | Type |                   Extra
-----------+------+--------------------------------------------
 x         | int4 | not null default nextval('oo_x_seq'::text)
 y         | text |
Index: oo_x_key

... Another session gets a new sequence number, but mine is the same

test=> select currval('oo_x_seq');
 currval
---------
       1
(1 row)



--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026