Using autogenerated primary key in transaction

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Using autogenerated primary key in transaction
Date: 2010-01-02 16:40:11
Message-ID: 3083D1B79E6F431CB25707E5AAC97DB6@andrusnotebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2010-01-02 16:46:40 SELECT does not find table created by itself
Previous Message Dimitri Fontaine 2010-01-02 10:44:42 Re: pg_dump excluding tables content but not table schema