From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | MT <mt(at)open2web(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Avoiding a race condition |
Date: | 2002-11-23 17:05:36 |
Message-ID: | 1038071136.27507.427.camel@linda.lfix.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, 2002-11-23 at 15:00, MT wrote:
> Earlier I wrote a note asking how I could insert a record and then
> display that record as verification that it had been successfully
> inserted. I got several suggestions, the following which I'd like to use:
>
> SELECT nextval('my_sequence') as id
>
> Then do the insert with the sequence and all other operations with the
> "id".
>
> I've tried to do this without success. Could someone show me the syntax
> since I can't figure it out.
>
> I've tried:
>
> SELECT nextval('prodid_seq') as id;
>
> INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
^^^^^^^
That should be currval() or else you will increment the sequence twice.
> OK up to this point.
Not quite!
> Now how do I SELECT back the record I just inserted.
>
> SELECT * FROM product WHERE prodid = id;
> ERROR: Attribute 'id' not found
SELECT * FROM product WHERE prodid = currval('prodid_seq');
>
> 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.
Just keep using currval() until you want to increment the sequence
again.
(If you're using a procedural language, you can store the output from
the first use of nextval() in a variable; then you can use that instead
of currval().)
--
Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
LFIX Limited
From | Date | Subject | |
---|---|---|---|
Next Message | Medi Montaseri | 2002-11-23 18:37:03 | Re: Dumb Newbie Question - Mandrake 9.0 / PGSQL 7.2 |
Previous Message | MT | 2002-11-23 15:00:33 | Avoiding a race condition |