Re: simulating INSERT return values with default values

You can use currval function

This takes the current value from the session you are using to connect to
the database. Provided you do not open another session then this should
suffice.

However i would be inclined to use transactions just to be on the safe
side

begin;
insert into foo values ('tmp');
select currval('foo_id_seq');
commit;

HTH

On Mon, 22 Jul 2002, Thomas F. O'Connell wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Darren Ferguson