Using autogenerated primary key in transaction

["Andrus" <kobruleht2@xxxxxx>]

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.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general