Re: [SQL] Getting primary key from insert statement

At 00:46 +0300 on 03/06/1999, Pham, Thinh wrote:

> Hi, is there a way you can get back the primary key (serialized) from an
> insert you just do on a table. For example i have a table called "order" and
> i want to get the pid of that record so i can use it in the "orderdetail"
> table. It would be nice if that same insert statement could also return the
> pid.

Yes. You have to know the name of the sequence which the serial type caused
to be created. You are notified of the name when you create the table. It
would be 'order_pid_seq' in the case you described (remember that ORDER is
a reserved word, though).

Anyway, after you make the insert, you can retrieve the recently-created
value of the sequence, using currval( 'order_pid_seq' ).

This returns the value of the last invocation of nextval( 'order_pid_seq' )
in the current session. When you do an insert, it calls nextval internally.
And thus you will get a proper currval.

The question everybody asks is "but what if somebody else manages to insert
another row before I use currval?". This is not a concern, because currval
always returns the number your own session generated, and not anybody else.

Note that currval will not work until somebody does a nextval on the same
sequence.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma