From: Stefan Scheidegger <Stefan.Scheidegger@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: INSERT INTO relational tables
Date: Mon, 10 Dec 2007 11:42:04 +0100
Message-ID: <20071210104204.324340@gmx.net> (raw)
In-Reply-To: <20071209075032.2A1112E3294@postgresql.org>
References: <20071207172528.820B92C96D4@maia-2.hub.org>
<20071209075032.2A1112E3294@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Stefan.Scheidegger@gmx.net
Subject: Re: INSERT INTO relational tables
In-Reply-To: <20071210104204.324340@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox