Обсуждение: Subselect query for a multi table insert single query

Hello,
I am using Zope with Postgre with Psycopg. Now I have 4 related tables, and
would like to write a query which will input data from one html form.

So here are my tree sql statements, is there a way to make them into one:

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

3) 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">, <dtml-var last>);

This works for zope, where the last SQL Method calls a DTML Method "last"
which basically calls the 2nd SQL statement which returns the last value
from the business name sequence table.

Obvoiusly we come to the race condition realm here which may cause a foreign
key being allocated to the wrong business_address, should two users add a
record but the first user's line slows down for a fraction so that his
second sql method returns the value from the 2nd user -- you see my
problem;^)

What was suggested was to write a subselect query, which would first insert
the business_name, then selects the last_value from
business_name_business_name_seq  and then insert this value into the
business_address table ..... an so on. I would like to do this for all 4
tables!!!

Is this at all possible if so can you provide me with links to example code
or be good enough to reply to me.

many thanks

Norm

"Norman Khine" <norman@khine.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-sequence.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

 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@sss.pgh.pa.us>
> To: "Norman Khine" <norman@khine.net>
> Cc: "Pgsql-Novice" <pgsql-novice@postgresql.org>
> Sent: Sunday, September 22, 2002 11:03 PM
> Subject: Re: [NOVICE] Subselect query for a multi table insert single
query
>
>
> > "Norman Khine" <norman@khine.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
> >
>

"Norman Khine" <norman@khine.net> writes:
>  What should be the correct syntax for embedding this.

This would work fine:

INSERT INTO table VALUES (foo, bar, baz, currval('seq'), ...);

The elements of an INSERT/VALUES list are expressions, not
necessarily literal constants.

            regards, tom lane

Many thanks

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Norman Khine" <norman@khine.net>
Cc: "Pgsql-Novice" <pgsql-novice@postgresql.org>
Sent: Monday, September 23, 2002 5:18 AM
Subject: Re: [NOVICE] Subselect query for a multi table insert single query


> "Norman Khine" <norman@khine.net> writes:
> >  What should be the correct syntax for embedding this.
>
> This would work fine:
>
> INSERT INTO table VALUES (foo, bar, baz, currval('seq'), ...);
>
> The elements of an INSERT/VALUES list are expressions, not
> necessarily literal constants.
>
> regards, tom lane
>

Hi Tom,
I tried this on the command prompt but got the following error:

admin=> INSERT INTO business_address (street_name, town, county, postcode, county_id, business_name_id) values
('xcasc','ascasc', 'ascasc', 'acasc', 2, currval('business_name_business_name_seq')); 
ERROR:  business_name_business_name_seq.currval is not yet defined in this session

Any ideas as to what is going on.

Cheers

Norm