Re: Getting OID after Insert

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