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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
