[thelist] Postgre and Cold Fusion

[Jeff Howden]

Joshua,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: Joshua Olson
> 
> I'm testing Cold Fusion with PostGRE for the first REAL
> time.  I'm trying to determine the best way to find the
> number of the last inserted sequence and am running
> into numerous problems.
> 
> I've tried to implement a trigger to select the value
> on insert (as I would do with SQL Server).  I can't seem
> to make this work or find any examples to put me on the
> right track.
> 
> I've tried performing a SELECT curval('sequence_name')
> after the insert.  What I find is that the call returns
> the last value even between page loads, which means it's
> not a thread safe method.
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

Well, curval() is one way.  Just because you can call it between page loads
and get the same value doesn't mean it's not thread safe.  Rather, it means
that nothing has happened to the sequence you're requesting the current
value for so therefore it's still the same.  You can either tie it into a
<cftransaction> block or, if the Postgresql drivers for ColdFusion support
it, run the insert and select curval() queries in the same <cfquery> block.


Alternatively, you could swap the order of your queries and swap curval()
for nextval(), then insert the newly created id.

Additionally, I would recommend setting your table up like this:

CREATE TABLE tablename
(
  id int4 default nextval('sequencename'),
  ...
);

Now you can insert the value yourself in situations where that's necessary
or have the database do it for you the rest of the time.

Good luck,

Jeff
------------------------------------------------------
Jeff Howden - Web Application Specialist
Resume - http://jeffhowden.com/about/resume/
Code Library - http://evolt.jeffhowden.com/jeff/code/