RE: INSERT ... RETURNING as Oracle

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(at)postgresql(dot)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(at)gmx(dot)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(at)postgresql(dot)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
**********************************************************************