Re: Getting OID after Insert

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