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.
