From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: INSERT INTO relational tables |
Date: | 2007-12-10 11:29:15 |
Message-ID: | 20071210112915.GF917@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
am Mon, dem 10.12.2007, um 12:08:48 +0100 mailte A. Kretschmer folgendes:
> 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.
a little example:
test=# create table customer (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence "customer_id_seq" for serial column "customer.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
CREATE TABLE
test=*# create table orders (customer int references customer, val int);
CREATE TABLE
test=*# create or replace function orders_insert (text, int) returns int
as $$declare i int;begin select into i id from customer where name=$1;
if i is null then insert into customer (name) values ($1); select into i
currval('customer_id_seq'); end if; insert into orders values (i,$2);
return i; end; $$language plpgsql;
CREATE FUNCTION
test=*# select * from customer;
id | name
----+------
(0 rows)
test=*# select * from orders ;
customer | val
----------+-----
(0 rows)
test=*# select orders_insert('foo',1);
orders_insert
---------------
1
(1 row)
test=*# select orders_insert('foo',2);
orders_insert
---------------
1
(1 row)
test=*# select orders_insert('foo',3);
orders_insert
---------------
1
(1 row)
test=*# select orders_insert('bar',4);
orders_insert
---------------
2
(1 row)
test=*# select * from customer;
id | name
----+------
1 | foo
2 | bar
(2 rows)
test=*# select * from orders ;
customer | val
----------+-----
1 | 1
1 | 2
1 | 3
2 | 4
(4 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Scheidegger | 2007-12-10 13:21:13 | Re: INSERT INTO relational tables |
Previous Message | A. Kretschmer | 2007-12-10 11:08:48 | Re: INSERT INTO relational tables |