Re: SQL-question: returning the id of an insert querry

After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.

Hope this helps,

On Sun, Nov 09, 2003 at 11:29:49AM +0100, Andreas Fromm wrote:
> Hi,
>
> Im building an user database with many tables keeping the data for the
> Address, Phone numbers, etc which are referenced by a table where I keep
> the single users. My question is, how do I get the "Id"-value of a newly
> inserted address to store it in the referencing user table:
>
> (a)  INSERT INTO address VALUES (....);
>
> (b)  INSERT INTO users VALUES ( name, ... , address , ... );
>
> where address should hold the value of the Id from the Adress table.
>
>
> Do have to do an
> SELECT id FROM address WHERE oid = oid_returned_by_insert(a)
> or something like that after doing the insert(a) to get the correct id
> value, or is there a better way to do this.
>
> Im writing my app in Perl with DBD/DBI
>
>
> Thanks in advance,
>
> Andreas Fromm
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato