Re: automatic sequence number?

Rod,

Very cool indeed, never thought of that


Dave
On Sun, 2003-11-02 at 10:06, Rod Taylor wrote:
> On Sun, 2003-11-02 at 09:28, apnv3@muha.net wrote:
> > Hello.
> >
> > I have a table with one field acting as primary key, the key is
> > retrieved from a sequence-generator in postgresql.
> > Currently I am executing two queries for every insert;
> >
> > Query #1 retrieves a number from a sequence-generator, and places it into the
> > record.
> >
> > Query #2 performs the actual insert.
> >
> > Does anyone have an idea how to cut this down to only one query?
> > And maybe somehow get back the primary key in the ResultSet.
> >
> > Or maybe this is how it is supposed to be done, it is just that I am not
> > very experienced in jdbc/sql to know. :)
>
> If you don't mind it being a little ugly, send both SQL queries in a
> single command.
>
> sql = "INSERT INTO ... VALUES (DEFAULT, 'data1', 'data2');";
> sql += "SELECT currval('sequence')";
>
> Prepare and use sql. DEFAULT is the primary key value.
>
> You should get back a result set of the sequence iff the insert
> succeeds.
>
> It is still technically 2 queries, but only has the network overhead of
> one.