Re: Efficient Insert statement for one record into multiple

[Dave Cramer <pg(at)fastcrypt(dot)com>]

On 18-Dec-06, at 9:36 AM, Albe Laurenz wrote:

> > > I have this task that I need to insert one user record into to  
> > > tables
> A
> > > and B.
> > >
> > > 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.
> >
> > 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.
>
> This is _bad_.
No, this works
> What if somebody did a nextval("primary_A") between the insert into A
> and the insert into B?
as long as you use currval then it works as advertised
> If there is a sequence "primary_A" for table A, you should proceed
> as follows:
This requires two trips to the db. Roland's solution doesn't, and it  
does work.
> - select nextval("primary_A")
> - then insert into A with this primary key
> - then insert into B with the same key
>
> That should all be in a single transaction!
This doesn't even have to be in a single transaction.

Dave
> Yours,
> Laurenz Albe
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org