Обсуждение: 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
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 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/
> 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
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: > 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.
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
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:
> 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/ > 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