Re: Subselect query for a multi table insert single query

Hi Tom,
Thanks for this, would the sql be something like

INSERT INTO business_address (business_name_id) select
currval('business_name_business_name_seq');

Is this correct? If so, how do you Insert the other values that are not
associated with the sequence i.e address, town etc....

can i do something like

INSERT INTO business_address (street_name, town, county, postcode,
county_id, business_name_id) values (<dtml-sqlvar street_name
type="string">, <dtml-sqlvar town type="string">, <dtml-sqlvar county
type="string">, <dtml-sqlvar postcode
type="string">, <dtml-sqlvar county_id type="int">, select
currval('business_name_business_name_seq');

This does not work?!As I get a parse error near "select"

What should be the correct syntax for embedding this.

Thanks

Norm
----- Original Message -----
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: "Norman Khine" <norman(at)khine(dot)net>
> Cc: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
> Sent: Sunday, September 22, 2002 11:03 PM
> Subject: Re: [NOVICE] Subselect query for a multi table insert single
query
>
>
> > "Norman Khine" <norman(at)khine(dot)net> writes:
> > > 1) INSERT INTO business_name (business_name, business_url)
> > > values ('<dtml-var business_name>', '<dtml-var business_url>');
> >
> > > 2) select last_value from business_name_business_name_seq
> >
> > Instead use
> >
> > select currval('business_name_business_name_seq');
> >
> > to get the assigned sequence value without a race condition. See
> >
>
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-sequen
> ce.html
> >
> > Actually you don't need to bother with the separate select, unless
> > your client code needs that ID for other purposes. You could just
> > write the currval() call in the second INSERT.
> >
> > regards, tom lane
> >
>