Re: Using a serial primary key as a foreign key in a second

>   Nathaniel <naptrel@yahoo.co.uk> wrote:
> So, I want to add the company "Looney Tunes" (which is auto-assigned
> a company_id value), and then extract that id value so that I can use
> it in the company_id foreign key field of Bugs Bunny's person record.


http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.2

4.15.2) How do I get the value of a SERIAL insert?

One approach is to retrieve the next SERIAL value from the sequence
object with the nextval() function before inserting and then insert it
explicitly. Using the example table in 4.15.1, an example in a
pseudo-language would look like this:

     new_id = execute("SELECT nextval('person_id_seq')");
     execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise
Pascal')");

You would then also have the new value stored in new_id for use in other
queries (e.g., as a foreign key to the person table). Note that the name
of the automatically created SEQUENCE object will be named
<table>_<serialcolumn>_seq, where table and serialcolumn are the names
of your table and your SERIAL column, respectively.

Alternatively, you could retrieve the assigned SERIAL value with the
currval() function after it was inserted by default, e.g.,

     execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
     new_id = execute("SELECT currval('person_id_seq')");

Finally, you could use the OID returned from the INSERT statement to
look up the default value, though this is probably the least portable
approach. In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid
value is made available via $sth->{pg_oid_status} after $sth->execute().




   I am not sure if this is exactly what you might need. It also does
not specify if this would cause problems for concurrent users or not.
Hope it helps out though.

Patrick