Thread: Using autogenerated primary key in transaction

INSERT generates SERIAL primary key column value automatically.

How to save this value for use in same transaction ?

Only way I have found is to create temporary table.

To emulate real database table for testcase I used command:

create temp table test ( vmnr serial primary key, name text ) on commit
drop;

Then  I tried

CREATE TEMP TABLE savedkey  ON COMMIT DROP AS
INSERT INTO test ( name)
select 'Scott'
RETURNING vmnr;

and

CREATE TEMP TABLE savedkey  ON COMMIT DROP AS
SELECT * FROM (INSERT INTO test ( name)
select 'Scott'
RETURNING vmnr) dummy;

but got syntax errors.

lastval() in infected by triggers and nextval() requires hard-coded sequence
name.
How to get autogenerated primary key for >=8.0 servers ?
If this is not possible then  at least for
PostgreSql versions which support ON COMMIT DROP and RETURNING clauses
?

Andrus.

Andrus wrote:
>
> How to get autogenerated primary key for >=8.0 servers ?


currval('sequencename')

In response to Andrus :
> INSERT generates SERIAL primary key column value automatically.
>
> How to save this value for use in same transaction ?
> How to get autogenerated primary key for >=8.0 servers ?

There are some functions to get that value:


test=# create table andrus(id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "andrus_id_seq" for serial column "andrus.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "andrus_pkey" for table "andrus"
CREATE TABLE
test=*# insert into andrus values (default);
INSERT 0 1
test=*# select currval(pg_get_serial_sequence('andrus','id'));
 currval
---------
       1
(1 row)


HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99