Thread: Using curr_val Wisely

Hi all,

I've got one daft question, and one good one...


Good One:

I'm using curr_val('blah_id_seq') in a transaction like so: (in pseudo SQL)

BEGIN;
INSERT INTO blah;
INSERT INTO foo (currval('blah_id_seq'));
COMMIT;

So the second insert requires the primary key of the first table as a
reference.

In the documentation, it says that even when there's multiple users, the
back end essentially guarantees that curr_val will work in this case. How
does it distinguish? Within the transaction, connection or something else?

Just wondering so I don't stuff anything up too badly! :)


Daft One:

This would be asked all the time, I'm sure. If I want to return the primary
key, or oid of the inserted record. The best way I can figure to do that now
is by selecting curr_val('blah_id_seq') at the end of this transaction
(which is why I asked the above question, because I'd like to be sure it
returns the id I think it should!)

For some reason, I can't get the postgres interface in PHP to return the
oid... But I'm more interested in doing this as a transaction anyway. :)


- Jeff


-- jdub@aphid.net --------------------------------- http://linux.conf.au/ --

              linux.conf.au - coming to Sydney in January 2001

    Installing Linux Around Australia - http://linux.org.au/installfest/

> I'm using curr_val('blah_id_seq') in a transaction like so: (in
> pseudo SQL)

curr_val() returns the current value of the sequence, in the current
transaction.
So an insertion in transaction A won't affect the value of it in existing
transaction B.




Regards,

Andrew

"Andrew Snow" <als@fl.net.au> writes:
>> I'm using curr_val('blah_id_seq') in a transaction like so: (in
>> pseudo SQL)

> curr_val() returns the current value of the sequence, in the current
> transaction.

More precisely, currval() returns the same value returned by the most
recent nextval() on that sequence in the current backend --- transaction
boundaries don't matter AFAIR.

If you try currval() without ever having executed nextval() in the
current session, you will see this is so.

(I believe a setval() also causes currval() to become set, but that's
a special case that seldom matters.)

So you need not worry about other backends when using currval().  If you
have rules or triggers that can cause the sequence to get advanced, then
you could get confusing results if you forget these rules/triggers are
being fired.

            regards, tom lane