Re: Select nextval problem

----- Original Message -----
From: "MT" <mt(at)open2web(dot)com>
Sent: Friday, November 22, 2002 7:46 PM

> SELECT nextval('prodid_seq') as id;
>
> INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
>
> OK up to this point.
>
> Now how do I SELECT back the record I just inserted.
>
> SELECT * FROM product WHERE prodid = id;
> ERROR: Attribute 'id' not found
>
> OK, I'm not very good at SQL yet. How do you put the sequence number in
> a place holder, then do the insert, and finally retrieve the record you
> just inserted by matching the prodid with the number in the place holder.

I'm not an expert myself, but it seems that your problem is probably more
than pure SQL. I think you wish an automated something (a client program,
for example) to do the SELECT and work with its result. Tell us more (is it
C, plpgsql or something else?)

1. SELECT nextval(...) increases prodid_seq, so calling nextval(...) in
INSERT increases it again, causing the one you selected into id (which could
only be seen in that query's result set as attribute 'id') to be lost. So
the first SELECT is not needed.

2. There is something called currval(...) that doesn't increase the counter,
just returns its current value. In our lucky case, you need something that
can be queried with this function, so I'd say either of the following will
do:

SELECT * FROM product WHERE prodid = currval('prodid_seq');
SELECT * FROM product ORDER BY prodid DESC LIMIT 1;

Both of these, however, assume that you haven't inserted any rows after the
one in question.

3. In plpgsql, use "SELECT INTO _id nextval(...);" where _id is a local
variable, and use it in the select.

4. In C, use whatever tools you have in your C version, etc.

HTH,
G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------