Thread: Getting OID after Insert

Getting OID after Insert

From

Bruce Cota

Date:

18 October 2001, 13:15:44

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

Re: Getting OID after Insert

From

Martijn van Oosterhout

Date:

18 October 2001, 14:07:26

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.

Re: Getting OID after Insert

From

Martijn van Oosterhout

Date:

18 October 2001, 15:23:25

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.

Re: Getting OID after Insert

From

Tom Lane

Date:

18 October 2001, 17:11:57

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

Re: Getting OID after Insert

From

Bruce Cota

Date:

19 October 2001, 11:10:40

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.

Re: Getting OID after Insert

From

"Merlin Moncure"

Date:

23 October 2001, 20:54:31

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

Re: Getting OID after Insert

From

Kevin HaleBoyes

Date:

24 October 2001, 12:11:11

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
>>
>
>

Re: Getting OID after Insert

From

Martijn van Oosterhout

Date:

24 October 2001, 12:51:59

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.

Re: Getting OID after Insert

From

Bruce Momjian

Date:

24 October 2001, 20:01:28

> 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