libpq: usage of PQoidValue to obtain serial primary key after insert

Поиск
Список
Период
Сортировка
От Michal Dobaczewski
Тема libpq: usage of PQoidValue to obtain serial primary key after insert
Дата
Msg-id 40FFA197.9080106@cc.com.pl
обсуждение исходный текст
Ответы Re: libpq: usage of PQoidValue to obtain serial primary key after insert  (Peter Eisentraut <peter_e@gmx.net>)
Список pgsql-interfaces
Hello,

First of all forgive me if this has been done before. I searched the 
list and found some ontopic answers but none formulated exactly this way.

Let's assume I have a test table:

create table test( id serial, data text );

I'm using libpq to execute an insert:

insert into test( data ) values( 'aaaaaa' );

which generates a new serial primary key. I need to obtain this key back 
into the application in order to use it elsewhere. So far my approach 
has been to use PQoidValue to obtain the last inserted oid and then 
explicitly do:

select id from test where oid = <oid>

Browsing the general list I've just read a post claiming that oid are 
not guaranteed to be unique in the table. This is also stated in the 
documentation - which seems to have escaped me this far. So I assume the 
approach presented above is flawed.

I would like to ask what is a most elegant way to deal with such issues?

I know I can do this:

select nextval( <sequence> );

insert into test values( <newid>, 'aaaaa' );

or:

insert into test( data ) values( 'aaaaaa' );

select currval( <sequence> );

But neiher seems particulary appealing to me because we have a little 
wrapper library we use for various projects and:

1) I would prefer not to make any assumptions about the name of the 
sequence that is created by postgres for the serial type (possibly it is 
a temp table I have just created from the code or something like that).

2) I would prefer not to make assumptions about the need for the 'select 
nextval' prior to insert since some tables might not need this at all.

3) I can imagine the primary key defaulting to some other value (i.e. 
generated by some function) where there would be no race condition proof 
currval equivalent.

So to reasume my question:

The ideal situation for me would be if I just could fire the insert with  the primary key being automagically generated
bysome (any) default 
 
(doesn't have to be a seqence) and then (if I need to) to be able to 
reach back for the primary key that was actually inserted. So far I've 
been achieving it with select ... where oid = ..... This is most 
probably wrong. Is there any other way?

Your help is very much appreciated,

Regards,

Michal Dobaczewski.


В списке pgsql-interfaces по дате отправления:

Предыдущее
От: Arguile
Дата:
Сообщение: Re: perl, python, Tcl client interfaces
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: libpq: usage of PQoidValue to obtain serial primary key after insert