Re: INSERT INTO relational tables

At 09:23 AM 12/7/2007, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Fri, 07 Dec 2007 14:22:26 +0100
>From: "Stefan Scheidegger" <Stefan(dot)Scheidegger(at)gmx(dot)net>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: INSERT INTO relational tables
>Message-ID: <20071207132226(dot)281710(at)gmx(dot)net>
>
>Hi all
>
>I'm new to SQL and I'm facing a problem I can't
>find any information about (google, forums). By
>now I wonder if I understood something wrong about relational DBs.
>
>An example to explain my Problem:
>Lets say I have a table containing information
>about the customer (name, address, ...) and
>about his order (pieces, product-name, price).
>Because one customer can order several products
>I split the table into two relational tables to prevent redundancy:
>
>tbl_customer (cust_id, cust_name, cust_address)
>and
>tbl_order (ord_pieces, ord_productname,
>ord_price, ord_customer REFERENCES tbl_customer(cust_id))
>
>Now I want to insert several new customers with
>several orders each at once. If I had all
>information in one table, this would be easy with something like:
>
>INSERT INTO tbl_customerorders (name, address,
>pieces, porductname, price) VALUES ('MR. X',
>'1st street', 3, 't-shirts', 30), ('MR. X', '1st
>street', 5, 'books', 50), ('MRS. Y', '2nd
>street', 1, 't-shirt', 10),...
>
>But how can I do this in one query if I split
>the table? I can add one new customer, get his
>ID with curval() and then add his orders. But
>this won’t work if I want to add several customers at once.
>
>To read this information I can do a query with
>the argument WHERE cust_id=ord_customer. I can
>create a VIEW doing this so I can read the data
>as if it was stored in only one table. But is
>there in posgres/sql an abstraction-layer that
>allows me to insert as if the information was
>stored in one table? (Something like a VIEW that
>provides INSERT, UPDATE, … and automatically
>inserts the referenced ID.)
>
>Thanks for any help!
>Stefan

Andrea has given you some "deep" answers (i.e.
smarter than what I'm going to say) but since you
say you're a newbie to SQL maybe this will help.

Perhaps all you need is to wrap your entire set
of statements into a transaction
(http://www.postgresql.org/docs/8.2/interactive/tutorial-transactions.html)
to ensure that your entires are "acid"
(http://databases.about.com/od/specificproducts/a/acid.htm).

BEGIN
insert tbl_customer...;
insert tbl_order...;
insert tbl_order...;
END

It's not clear whether you're looking for syntax
simplicity or referential integrity (or both). If
syntax is your need, then Andreas has given some good ideas.

As another thought about syntax enhancement:
there are lots of object-relational mappers out
there that let you model your relations in a
higher level language (I use Ruby and
ActiveRecord which are easy to write and learn,
but Java, .NET, etc all have great stuff). Then
you just build your "entities" in the domain
specific language and it handles all the id
inserts and relational mapping for you. They can
even handle mapping many-to-many joined entities,
if you're careful in setting it up.

I hope this is helpful,

Steve

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

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

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

-------- Original-Nachricht --------
> Datum: Mon, 10 Dec 2007 12:29:15 +0100
> Von: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
> An: pgsql-sql(at)postgresql(dot)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