Re: Problems with transactions and sequences

On Thu, 28 Aug 2003 10:51:21 -0400, in
comp.databases.postgresql.general you wrote:

>Sune Nielsen <sunegn@image.dk> writes:
>> Now, the problem is that I wish to extract the sequence number from a
>> newly inserted user, like this:
>
>> INSERT INTO Users (name) VALUES ('JohnDoe');
>> SELECT CURRVAL('users_bid_seq');
>
>> This works perfectly(!), but my project involves multiple simultanous
>> users so I have to use transactions like this:
>
>> BEGIN;
>> INSERT INTO Users (name) VALUES ('JohnDoe');
>> SELECT CURRVAL('users_bid_seq');
>> COMMIT;
>
>No, you don't need the BEGIN/COMMIT.  Read the description of currval
>again.
Okay, I think I get it. Because of the current connection-session I'm
safe?

>
>> This, however, doesn't work although I thought it should. It doesn't
>> return anything at all?!?!
>
>Surely it does.  You didn't say exactly what client-side code you are
>using, but I suspect it's looking at the result of the COMMIT step
>(ie, nothing) instead of the result of the prior SELECT.
Sorry about that. I was querying using SQL through phpPgAdmin's
SQL-interface although I was merely prototyping for some PHP-code I
couldn't get to work. But for the reference, how do I obtain the
results from an SELECT-statement if psql only returns the value of
COMMIT?

Thanks for you very speedy reply!
--
Sune Nielsen sunegn@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk