INSERT ... RETURNING as Oracle

Hi,

Sample table:

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

How can I make a one step insert into the table and get values of 'ID' ?
I am trying to find a similar solution as in the oracle's INSERT ...
RETURNING.

If I use at first the INSERT, and after SELECT MAX(ID), the result will be
uncertain.

my system version: 7.0.2

Thx,
Andras s-andras@freemail.hu

"Sipos Andras" wrote: >create table basket (

id serial NOT NULL PRIMARY KEY,
timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

How can I make a one step insert into the table and get values of 'ID' ?
I am trying to find a similar solution as in the oracle's INSERT ...
RETURNING.

If I use at first the INSERT, and after SELECT MAX(ID), the result will be
uncertain.

The serial data type is actually an INT4 with a sequence, as you will have
seen when you created your table. Use currval after the insert to get the
latest value of the sequence in your current session.

junk=# create table basket (
junk(# id serial NOT NULL PRIMARY KEY,
junk(# timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
junk(# );
NOTICE: CREATE TABLE will create implicit sequence 'basket_id_seq' for SERIAL column 'basket.id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'basket_pkey' for table 'basket'
CREATE
junk=# insert into basket (timestamp) values (now());
INSERT 2091655 1
junk=# select currval('basket_id_seq');
currval
---------
1
(1 row)

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Give, and it will be given to you. A good measure,
pressed down, taken together and running over,
will be poured into your lap. For with the same
measure that you use, it will be measured to
you." Luke 6:38

Sipos Andras writes:

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

How can I make a one step insert into the table and get values of 'ID' ?
I am trying to find a similar solution as in the oracle's INSERT ...
RETURNING.

We have this sort of thing on the wish list, but it doesn't exist yet.
Some people feel it's better to calculate the default in the client
program and insert it explicitly. This may be a reasonable alternative
for some applications.

If I use at first the INSERT, and after SELECT MAX(ID), the result will be
uncertain.

If you are worried about other transactions getting in the way then you
should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

And using MAX is also horrifically slow once you start having any
significant volumes of data.

Why not write a function to add the info, which selects from a sequence,
inserts the new row using the sequence number, and then returns the sequence
number? I've done it a number of times, and it's been quite successful so
far.

Cheers...

MikeA

-----Original Message-----
From: Peter Eisentraut
To: Sipos Andras
Cc: pgsql-general@postgresql.org
Sent: 3-4-01 8:04 PM
Subject: Re: [GENERAL] INSERT ... RETURNING as Oracle

Sipos Andras writes:

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

How can I make a one step insert into the table and get values of 'ID'

?

I am trying to find a similar solution as in the oracle's INSERT ...
RETURNING.

We have this sort of thing on the wish list, but it doesn't exist yet.
Some people feel it's better to calculate the default in the client
program and insert it explicitly. This may be a reasonable alternative
for some applications.

If I use at first the INSERT, and after SELECT MAX(ID), the result

will be

uncertain.

If you are worried about other transactions getting in the way then you
should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

On Sun, 4 Mar 2001, Peter Eisentraut wrote:

If I use at first the INSERT, and after SELECT MAX(ID), the result will be
uncertain.

If you are worried about other transactions getting in the way then you
should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE.

I have some uncertainty about somethings that I've read from the FAQ.

If I have a client INSERT a value in a table (all happens inside a BEGIN
WORK ---- COMMIT WORK), which has a SERIAL column, and I want to
reference that serial value on another INT column which is a foreign key
of that SERIAL. Using currval() function will give me exact value as the
FAQs say, and why? Is it the transaction that makes this work this way?

Saludos... :-)

System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

"Martin A. Marques" <martin@math.unl.edu.ar> writes:

If I have a client INSERT a value in a table (all happens inside a BEGIN
WORK ---- COMMIT WORK), which has a SERIAL column, and I want to
reference that serial value on another INT column which is a foreign key
of that SERIAL. Using currval() function will give me exact value as the
FAQs say, and why? Is it the transaction that makes this work this way?

No, it has nothing to do with transaction boundaries; it'd work the same
even if you did the currval() in a later transaction. The reason it
works is that each backend remembers the last nextval() result it got
for each sequence it's nextval'd in the current session. currval() just
pulls that value out of the local table without ever touching the shared
sequence object. Simple, eh?

Of course, you can still get burnt if you have triggers and such that
do nextval()s on the same sequence your main-line code is touching.
Then one of them might do a nextval() "behind your back", so to speak.
But that sort of foulup is a straight programming error that doesn't
have anything to do with parallel behavior of multiple clients.

regards, tom lane

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