Re: INSERT INTO relational tables

Thanks for your hints so far.

I'm looking for both syntax simplicity and referential integrity. I'm using c++ with libpqxx, but I didn't find a good object-relational mapper. And I'm too lazy to implement a whole persistency layer as I don't have a huge DB with many relations.

I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I created the VIEW composing my table with customers and orders:

CREATE VIEW vw_customer_insert AS
SELECT * FROM tbl_customer, tbl_order
WHERE cust_id = ord_customer;

Then I added a rule for the inserting:
CREATE RULE rl_customer_insert AS
ON INSERT TO vw_customer_insert DO INSTEAD
( INSERT INTO tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address);
INSERT INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname, NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"'));
);

But this results in the same problem: It works fine if I insert just one new customer with one new order. But if I want to insert several new entries:

INSERT INTO vw_customer_insert(cust_name, cust_address, ord_pieces, ord_productname, ord_price)
VALUES ), (‘MR. X’, ‘1st street’, 5, ‘books’, 50), (‘MRS. Y’, ‘2nd street’, 1, ‘t-shirt’, 10);

This doesn't work correctly; all orders are now related to the last customer. In this example, both orders are related to MRS. Y because I use currval() in my insert rule.

Do you see any solution for this? I thought that the performance of this would be better than INSERTing to tbl_customer, fetching the ID and then do several INSERTS to tbl_order in c++. But actually I tend to do it with several INSERT statements in one transaction, as Steve proposed.

Cheers Stefan

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