Thread: INSERT ... RETURNING as Oracle

Hi,

Sample table:

create table basket (
  id   serial  NOT NULL PRIMARY KEY,
  timestamp  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Can I how to make in one step insert into the table and get values of 'ID' ?
I find similar solution then oracle's INSERT ... RETURNING.

If first make INSERT, and after SELECT MAX(ID), then result is uncertain.

my system version: 7.0.2

Thx,
Andras

On Sat, 3 Mar 2001, Sipos Andras wrote:

> create table basket (
>   id   serial  NOT NULL PRIMARY KEY,
>   timestamp  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
> );
>
> Can I how to make in one step insert into the table and get values of 'ID' ?
> I find similar solution then oracle's INSERT ... RETURNING.
>
> If first make INSERT, and after SELECT MAX(ID), then result is uncertain.

The serial type will implicitly create a sequence called basket_id_seq.
This row in your table has a default value of nextval('basket_id_seq').
If you call currval('basket_id_seq'), you will get the last value
generated by the sequence.  This is similar to using <sequence>.CURRVAL in
Oracle.

-- Brett
                http://www.chapelperilous.net/~bmccoy/
------------------------------------------------------------------------
I will always love the false image I had of you.

Just wanted to add that PHP has a GETLASTOID function that will work
(after an insert) even if you don't use sequences/serials.

--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Perl, PostgreSQL