Re: Multi table insert

Hi and thanks for your replies.
The fix was as per Andreas' post and this works now.

Although, I would like to improve the code and make it better.

The ' business_name_business_name_seq' is just a sequence type for the table business_name where business_name table is related like:

business_name -> business_address by the business_name_id

as the submit form is just one form, i inserted the values in order so I needed the fk_business_name_id to be taken from the previous insert and so on...

perhaps there is better way to do this.

cheers

norman

George Pavlov <gpavlov(at)mynewplace(dot)com> wrote: can you replace:

business_name_business_name_seq.last_value

with:

(select last_value from business_name_business_name_seq)

or, more defensively coded:

(select max(last_value) from business_name_business_name_seq)

i am assuming business_name_business_name_seq is some kind of one-row
table that is always guaranteed (how?) to have one row and sort of fakes
a sequence (which brings the question of why not use a true sequence?)

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of norman
> Sent: Friday, October 27, 2006 5:08 AM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: [NOVICE] Multi table insert
>
> Hello,
> I have created a multi-table insert script in python, the
> problem is that this worked fine on an older PostgreSQL db
> (7.2) but now I get an
>
> Error, psycopg2.ProgrammingError: missing FROM-clause entry
> for table "business_name_business_name_seq"
>
> Here is part of the script
>
> It is a Z SQL Method for Zope.
>
> ##########
>
>
> INSERT INTO business_name (business_name, business_url,
> business_type_id)
> values (
,
>
,

> business_type_list_id type="int">);
>
>

>
> INSERT INTO business_address (street_name, town, city,
> postcode, county_id, business_name_id) values
> (
,
>
,
>
,
>
,
>
,
> business_name_business_name_seq.last_value);
>
> #########
>
> Here I want to take the last_value for the business_name_id
> that was generated in the first insert and put it as the
> related item in the next INSERT table.
>
> Is there a better way to do this? I have 4 tables all related
> and one input form.
>
> Many thanks
>
> Norman
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Send instant messages to your online friends http://uk.messenger.yahoo.com