Content-Type: text/plain; charset="utf-8"
Date: Mon, 10 Dec 2007 11:42:04 +0100
From: "Stefan Scheidegger" <Stefan.Scheidegger@gmx.net>
In-Reply-To: <20071209075032.2A1112E3294@postgresql.org>
Message-ID: <20071210104204.324340@gmx.net>
MIME-Version: 1.0
References: <20071207172528.820B92C96D4@maia-2.hub.org>
	<20071209075032.2A1112E3294@postgresql.org>
Subject: Re: INSERT INTO relational tables
To: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 8bit

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