Re: Insert data into multiple tables - Mailing list pgsql-general

On Tue, Apr 17, 2001 at 05:37:31PM -0500, K Old wrote:
> Hello,
>
> I have a general question about inserting data.
>
> I have the following tables:
>
> CREATE TABLE customer (
>     client_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
>     addr_id INT,
>     account_type INT,
>     how_hear INT,
>     cc_type INT,
>     cc_exp VARCHAR(5),
>     cc_num VARCHAR(16),
>     sign_up_date DATE
> );
>
> CREATE TABLE addresses (
>     addr_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
>     fname VARCHAR(16),
>     lname VARCHAR(16),
>     company VARCHAR(72),
>     addr1 VARCHAR(72),
>     addr2 VARCHAR(48),
>     city VARCHAR(32),
>     state VARCHAR(3),
>     zip VARCHAR(16),
>     country VARCHAR(32),
>     phone VARCHAR(18),
>     fax VARCHAR(18),
>     email VARCHAR(74)
> );
>
> I need to insert data into both of these tables at one time.  I want to have
> the "addr_id" field in the addresses tables to be included in the insert of
> data to the customer table.
>
> My solution so far is to have 1 insert statement that inserts the
> appropriate data into the addresses table (creating a record), then have a
> select statement pull back the most recent (which is only milliseconds old)
> record from addresses and get the addr_id (it would probably be set to a
> variable) then when all other data is gathered for the customer insert the
> addr_id would be included in that insert.
>
> This seems really difficult to do a simple insert and I was wondering if
> anyone knew if I could use something like a view that would allow me to
> insert into multiple tables with one statement?  I didn't see anything
> reguarding views in the documentation.

i've found lots of views-documentation. hmm! (on my debian 2.2
system it's under /usr/share/doc/postgresql-doc/html/* ...
look under 'create rule' and 'create view' for starters.)

how about something like

    create view newcust as
        select
            c.client_id,
            c.addr_id,
            c.account_type,
            c.how_hear,
            c.cc_type,
            c.cc_exp,
            c.cc_num,
            c.sign_up_date,
            a.addr_id
            a.fname,
            a.lname,
            a.company,
            a.addr1,
            a.addr2,
            a.city,
            a.state,
            a.zip,
            a.country,
            a.phone,
            a.fax,
            a.email
        from
            customers c,
            addresses a
        where
            c.addr_id = a.addr_id;

    create rule cust_insert as
        on insert to newcust
        do instead
        (
            insert into customers
                NEW.client_id,
                NEW.addr_id,
                NEW.account_type,
                NEW.how_hear,
                NEW.cc_type,
                NEW.cc_exp,
                NEW.cc_num,
                NEW.sign_up_date
            ;
            insert into addresses
                NEW.addr_id
                NEW.fname,
                NEW.lname,
                NEW.company,
                NEW.addr1,
                NEW.addr2,
                NEW.city,
                NEW.state,
                NEW.zip,
                NEW.country,
                NEW.phone,
                NEW.fax,
                NEW.email
            ;
        );

BUT -- if this is a 1:1 relation, and customers ALWAYS have
addresses, and addresses ALWAYS have customers, then just make
the sucker into one table and you're done.

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!