Skip site navigation (1) Skip section navigation (2)

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

From: Michal Dobaczewski <mdobaczewski(at)cc(dot)com(dot)pl>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: libpq: usage of PQoidValue to obtain serial primary key after insert
Date: 2004-07-22 11:14:31
Message-ID: 40FFA197.9080106@cc.com.pl (view raw or flat)
Thread:
Lists: 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 by some (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.

Responses

pgsql-interfaces by date

Next:From: Peter EisentrautDate: 2004-07-22 12:44:56
Subject: Re: libpq: usage of PQoidValue to obtain serial primary key after insert
Previous:From: Motsoeneng Paseka * Spoornet (GP)Date: 2004-07-22 07:17:55
Subject: Pgaccess questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group