From: Michal Dobaczewski <mdobaczewski@cc.com.pl>
To: pgsql-interfaces@postgresql.org
Subject: libpq: usage of PQoidValue to obtain serial primary key after insert
Date: Thu, 22 Jul 2004 13:14:31 +0200
Message-ID: <40FFA197.9080106@cc.com.pl> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: mdobaczewski@cc.com.pl
Subject: Re: libpq: usage of PQoidValue to obtain serial primary key after insert
In-Reply-To: <40FFA197.9080106@cc.com.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox