Обсуждение: Insert data into multiple tables

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.

Any ideas how to solve this?

Thanks,
Kevin
kevsurf4@hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

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,

is this the same as
    client_id SERIAL
?

>     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.

according to your table definition, addr_id is just an int, which
you apparently must supply to postgres if it's to be anything
but NULL. seems like you'd be able to use that same value for the
next insert.

i'd wrap the two inserts within a

    begin work;
    insert into addresses ....
    insert into customers ....
    commit work;

block, of course.

> 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.

if you're trying to get the value of client_id instead, if it's a
serial value, meaning it's got this neat trigger mechanism behind
the scenes to bump the counter for every insert, then you can get
your most-recently inserted serial value via

    select currval ( 'client_id_seq'::text );

(and if you do use the serial data type in defining a table, the
"\d mytable" will show how it's used to populate new instances of
that column:

 id | integer | not null default nextval('my_id_seq'::text)

so "nextval" bumps the counter for you, andonce you do that, you
can get the most recent value used via "currval()"

--
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!

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!

On Tue, 24 Apr 2001, will trillich wrote:

> On Tue, Apr 17, 2001 at 05:37:31PM -0500, K Old wrote:

>     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.

Yep. RULEs kick ass.

If this is a 1:1 relationship, and you don't mind wandering slightly
afield of the SQL standard, inheritance is a good way to go, too.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Tue, Apr 24, 2001 at 04:14:10PM -0400, Joel Burton wrote:
> On Tue, 24 Apr 2001, will trillich wrote:
> > 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.
>
> If this is a 1:1 relationship, and you don't mind wandering slightly
> afield of the SQL standard, inheritance is a good way to go, too.

okay. is there a way to have a function defined to accept an
inherited record type, that'll work in an inheritee-record?

    create table basic (
        v1 int4,
        v2 int4
    );
    create table dat (
        who varchar(30),
        id serial
    ) inherits (basic);
    create function calc(basic) returns int4 as '
        select $1.v1 + $1.v2
    ' language 'sql';

    ...

    select calc(dat);
    select calc(basic*);

is there a way?

--
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!