simulating INSERT return values with default values of sequences
Thomas F.O'Connell <tfo@monsterlabs.com>
From: "Thomas F. O'Connell" <tfo@monsterlabs.com>
To: pgsql-general@postgresql.org
Date: 2002-07-22T19:18:54Z
Lists: pgsql-general
i didn't see this question asked (or answered in the mailing lists), but it seems like it would be an FAQ... anyway, i'm just wondering if there's a slick way of any sort to be able to have a value generated from an INSERT available for immediate and secure re-use. for example, if i have CREATE SEQUENCE foo_id_seq; CREATE TABLE foo ( id int2 primary key default nextval( 'foo_id_seq' ), bar text ); and then later have INSERT INTO foo( bar ) VALUES( 'baz' ); is there any way safely to know what was inserted into the "id" field without encapsulating the INSERT statement in a transaction with whatever function needed that value? i'm guessing a transaction probably makes the most sense, right? e.g., BEGIN WORK; INSERT INTO foo( bar ) VALUES( 'baz' ); UPDATE foo SET bar = 'ola' WHERE id = ( SELECT last_value FROM foo_id_seq ); END WORK; if so, then i guess the next question is: is there a preference between a statement like UPDATE foo SET bar = 'ola' WHERE id = ( SELECT last_value FROM foo_id_seq ); and UPDATE foo SET bar = 'ola' WHERE id = ( SELECT MAX( id ) FROM foo ); thanks! -tfo