Thread: 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