currval question

Hi Andy,

this is AFAIK on a per transaction basis.

HTH
Tino Wildenhain

PS: I often use a plpsql script for creating table entrys, this helps if
you have many foreign keys and need some checks and the last id too. It
looks roughly like this:

CREATE FUNCTION ...

nextid=nextval(''sequence'');

insert into table ... (nextid, ... ) ;

return nextid;

this way you can use the function in another insert, immediately using its
return
value for insert in the other table.

--On Montag, 16. September 2002 18:14 -0400 Andy Kriger
<akriger@greaterthanone.com> wrote:

Andy,

I don't know exactly what's under the hood, but I know that currval() won't
even return a result unless you've used nextval() in your session. If
currval() did not operate solely within the scope of the session, it stands
to reason that it would return a result... It's behavior (from the driver's
seat) is consistent with a well conceived concurrency model. This should be
easy enough to test with a couple of terminals. My money is on Postgres! :)

Regards,
Garo.

on 9/16/02 3:14 PM, Andy Kriger at akriger@greaterthanone.com wrote:

I am trying to get the last value updated by an column auto-incrementing
with nextval(). In MySQL, you'd use LAST_INSERT_ID() - in Postgre, currval()
appears to do the trick.

Is this maintained on a per-connection basis? For example, user A inserts
and the nextval() updates to 5, user B does 2 inserts, updating nextval() to
7. When user A calls currval() they should get 5 if the updates are per-cnx.
What does psql do under the hood here?

thx
a

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

=-=-==-=-=-==

Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608

Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com

"Andy Kriger" <akriger@greaterthanone.com> writes:

I am trying to get the last value updated by an column
auto-incrementing with nextval(). In MySQL, you'd use
LAST_INSERT_ID() - in Postgre, currval() appears to do the trick.

Yes, currval is the thing to use to do this.

Is this maintained on a per-connection basis? For example, user A
inserts and the nextval() updates to 5, user B does 2 inserts,
updating nextval() to 7. When user A calls currval() they should get
5 if the updates are per-cnx. What does psql do under the hood
here?

currval is per connection. User A's currval would get them 5 and user
B would get 7. Basically as long as you are using the same connection
currval will do "The right thing"TM.

Jason

thx - i was eventually able to verify this is per connection