Обсуждение: simulating INSERT return values with default values of sequences

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

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

On Mon, Jul 22, 2002 at 02:18:54PM -0500, 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...

In fact, it _is_ a FAQ, but you didn't know what you needed was
currval().  Here's the entry:

http://www.ca.postgresql.org/docs/faq-english.html#4.15.3

A
--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110