Re: INSERT INTO relational tables

-------- Original-Nachricht --------
> Datum: Mon, 10 Dec 2007 12:29:15 +0100
> Von: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] INSERT INTO relational tables

> 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)
> 


Thanks a lot, this is exactly what I was looking for.

I realize I need to learn more about functions in SQL. Hopefully my questions will be more sophisticated next time ;-)


Greets Stefan


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger