Re: [GENERAL] 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026