Getting OID after Insert
Is there a way, in SQL, to access the oid of the row created
by an immediately preceding insert statement?
e.g.
insert into t (x, y) values (1, 2);
select * from t where oid = <what goes here?>
Thanks for any advice.
-Bruce
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
Thank you!
Hopefully that getlastoid refers to the last oid
created in this session or transaction? I can't find it
mentioned in the docs anywhwere.
Nextval and curval aren't specific to the transaction or
session, right? so that would be problematic in a
multi-user environment.
Martijn van Oosterhout wrote:
Show quoted text
On Wed, Oct 17, 2001 at 11:18:44PM -0400, Bruce Cota wrote:
Is there a way, in SQL, to access the oid of the row created
by an immediately preceding insert statement?e.g.
insert into t (x, y) values (1, 2);
select * from t where oid = <what goes here?>
Thanks for any advice.
Yeah, the is a getlastoid function.
sequences, nextval, currval and lastval are a better bet though.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
On Wed, Oct 17, 2001 at 11:18:44PM -0400, Bruce Cota wrote:
Is there a way, in SQL, to access the oid of the row created
by an immediately preceding insert statement?e.g.
insert into t (x, y) values (1, 2);
select * from t where oid = <what goes here?>
Thanks for any advice.
Yeah, the is a getlastoid function.
sequences, nextval, currval and lastval are a better bet though.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
On Thu, Oct 18, 2001 at 10:29:09AM -0400, Bruce Cota wrote:
Thank you!
Hopefully that getlastoid refers to the last oid
created in this session or transaction? I can't find it
mentioned in the docs anywhwere.
last insert, iirc.
Nextval and curval aren't specific to the transaction or
session, right? so that would be problematic in a
multi-user environment.
Sure they are. Currval returns the last value returned in this transaction.
This is a production database system and not having it multiuser safe would
be stupid no?
So you can do stuff like:
insert into invoice (customer,date,etc...);
insert into item (invoice_id, ...) values ( currval('invoice_id_seq'), ... );
etc...
multiuser safe. very cool. Remember, oid are not really guarenteed to be
unique...
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
Bruce Cota <bruce@vivi.com> writes:
Is there a way, in SQL, to access the oid of the row created
by an immediately preceding insert statement?
If you are writing a psql script, recent psql versions maintain
a LASTOID variable:
regression=# insert into int4_tbl default values;
INSERT 3357467 1
regression=# select :LASTOID;
?column?
----------
3357467
(1 row)
regression=#
regards, tom lane
I am not so sure how to do it with oid, but you can do this with a sequence.
A sequence is an autonumbering field which you can use for the p-key instead
of the oid. They are easy enough to create, (check the docs) and here is
the magic to get the key. Here is how I solved the problem. This approach
works over odbc.
create table test ( main_id serial );
the serial keyword makes a sequency and an index for the main_id column.
create function append_test()
returns int4
as '
insert into test default values;
select currval('test_main_id_seq''); '
language 'sql';
Thats it! now from an odbc client just fire off
select append_test
which will give you a cursor with the p-key as a field.
The downside to this approach is that it requires to sql statements to
create a new record, the append call and the update call to fill the row
with data.
Merlin
"Bruce Cota" <bruce@vivi.com> wrote in message
news:3BCE4A13.F815847@vivi.com...
Show quoted text
Is there a way, in SQL, to access the oid of the row created
by an immediately preceding insert statement?e.g.
insert into t (x, y) values (1, 2);
select * from t where oid = <what goes here?>
Thanks for any advice.
-Bruce
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
Is this actually guaranteed to work?
What happens if another call to append_test() is made such that
the insert takes place in the first call, then the insert takes place
in the second call before the select currval().
I've been looking for a way to do something similar to this (id field
takes its value from a sequence and returns the value).
In Oracle (sorry) I can execute an 'insert into ... returning main_id'
which avoids any timing issues and is very convenient!
K.
Merlin Moncure wrote:
Show quoted text
I am not so sure how to do it with oid, but you can do this with a sequence.
A sequence is an autonumbering field which you can use for the p-key instead
of the oid. They are easy enough to create, (check the docs) and here is
the magic to get the key. Here is how I solved the problem. This approach
works over odbc.create table test ( main_id serial );
the serial keyword makes a sequency and an index for the main_id column.
create function append_test()
returns int4
as '
insert into test default values;
select currval('test_main_id_seq''); '
language 'sql';Thats it! now from an odbc client just fire off
select append_test
which will give you a cursor with the p-key as a field.
The downside to this approach is that it requires to sql statements to
create a new record, the append call and the update call to fill the row
with data.Merlin
"Bruce Cota" <bruce@vivi.com> wrote in message
news:3BCE4A13.F815847@vivi.com...Is there a way, in SQL, to access the oid of the row created
by an immediately preceding insert statement?e.g.
insert into t (x, y) values (1, 2);
select * from t where oid = <what goes here?>
Thanks for any advice.
-Bruce
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
On Tue, Oct 23, 2001 at 02:38:04PM -0400, Kevin HaleBoyes wrote:
Is this actually guaranteed to work?
What happens if another call to append_test() is made such that
the insert takes place in the first call, then the insert takes place
in the second call before the select currval().
Yes, it's guarenteed to work. If you examine the documentation for
currval(), you will get the last value returned by nextval() *in this
session*. Whatever happens in other sessions is irrelevent.
I think this needs to be highlighted more in the documentation since many
people miss this very important point.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
Show quoted text
Magnetism, electricity and motion are like a three-for-two special offer:
if you have two of them, the third one comes free.
On Tue, Oct 23, 2001 at 02:38:04PM -0400, Kevin HaleBoyes wrote:
Is this actually guaranteed to work?
What happens if another call to append_test() is made such that
the insert takes place in the first call, then the insert takes place
in the second call before the select currval().Yes, it's guarenteed to work. If you examine the documentation for
currval(), you will get the last value returned by nextval() *in this
session*. Whatever happens in other sessions is irrelevent.I think this needs to be highlighted more in the documentation since many
people miss this very important point.
I have highlighted it more in the FAQ. Yes, many people miss that.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026