Re: INSERT INTO relational tables - Mailing list pgsql-sql

From A. Kretschmer
Subject Re: INSERT INTO relational tables
Date
Msg-id 20071210110848.GE917@a-kretschmer.de
Whole thread Raw
In response to Re: INSERT INTO relational tables  ("Stefan Scheidegger" <Stefan.Scheidegger@gmx.net>)
Responses Re: INSERT INTO relational tables
List pgsql-sql
am  Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger folgendes:
> 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 the new customer only into the table if this customer doesn't
exist there. If the customer exist in the table, obtain the id for this
customer.



simplified:

- detect, if the customer exists yes: obtain the id as id no: insert and use currval() as id
- insert the order with the id


I would write a function for this.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net


pgsql-sql by date:

Previous
From: "Stefan Scheidegger"
Date:
Subject: Re: INSERT INTO relational tables
Next
From: "A. Kretschmer"
Date:
Subject: Re: INSERT INTO relational tables