INSERT INTO relational tables - Mailing list pgsql-sql

From Stefan Scheidegger
Subject INSERT INTO relational tables
Date
Msg-id 20071207132226.281710@gmx.net
Whole thread Raw
Responses Re: INSERT INTO relational tables
List pgsql-sql
Hi all

I'm new to SQL and I'm facing a problem I can't find any information about (google, forums). By now I wonder if I
understoodsomething wrong about relational DBs.
 

An example to explain my Problem:
Lets say I have a table containing information about the customer (name, address, ...) and about his order (pieces,
product-name,price). Because one customer can order several products I split the table into two relational tables to
preventredundancy:
 

tbl_customer (cust_id, cust_name, cust_address)
and
tbl_order (ord_pieces, ord_productname, ord_price, ord_customer REFERENCES tbl_customer(cust_id))

Now I want to insert several new customers with several orders each at once. If I had all information in one table,
thiswould be easy with something like:
 

INSERT INTO tbl_customerorders (name, address, pieces, porductname, price) VALUES ('MR. X', '1st street', 3,
't-shirts',30), ('MR. X', '1st street', 5, 'books', 50),  ('MRS. Y', '2nd street', 1, 't-shirt', 10),...
 

But how can I do this in one query if I split the table? I can add one new customer, get his ID with curval() and then
addhis orders. But this won’t work if I want to add several customers at once.
 

To read this information I can do a query with the argument WHERE cust_id=ord_customer. I can create a VIEW doing this
soI can read the data as if it was stored in only one table. But is there in posgres/sql an abstraction-layer that
allowsme to insert as if the information was stored in one table? (Something like a VIEW that provides INSERT, UPDATE,
…and automatically inserts the referenced ID.)
 

Thanks for any help!
Stefan


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: polymorphic functions and domains
Next
From: Andreas Kretschmer
Date:
Subject: Re: INSERT INTO relational tables