Re: Efficient Insert statement for one record into multiple

[Roland Walter <roland(dot)walter(dot)rwa(at)gmx(dot)net>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Neu wrote:
> Hello,
> 
> I have this task that I need to insert one user record into to tables A
> and B.
> 
> User table A contains primary key and main data. Table B contains extra
> data for
> 
> statistics. Table A has one field Id which is a sequence so it gets a
> unique value
> 
> for each new insert. I would like to insert this value into the other
> table B as well as
> 
> foreign key.
> 
> So I’d like to do something like this
> 
> insert into A (user_name, value2 ) values ( “foo”, “foo2”)
> 
> AND
> 
> insert into B ( id_from_a , statistic_data) values ( 23, “bla”)
> 
> How is this done the best way? I would like to do this in one call.
> 
>  
> 
> Sorry, for his dumb question but I’m not very experienced in Postgresql. :o(
> 

If you know the name of the sequence for the primary key in table A, say
"primary_A", then use the following to insert into table B direct after
you inserted into table A in the same connection:

insert into B (id_from_a, statistic_data) values( currval("primary_A"),
bla")

This works only if your insertion into table A called in some form
nextval("primary_A"), may it be as default value or in the
insertion-statement itself.

Regards,
Roland.
- --
Dipl.-Phys. Roland Walter
mailto: roland (dot) walter (dot) rwa (at) gmx (dot) net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFFhntJxoOEAkary1ERAljXAJ9DASPP58I74Cj6Nt59rDzaIGlHjQCdFZRr
lFQGZaGY24/Sj77M6Le9xbc=
=Yr/X
-----END PGP SIGNATURE-----