I'm building a 3-dimensional array using 5 tables; 3 of them define the
headings for the 3 dimensions, a fourth states the valid combinations
of labels, and the 5th table matches combinations to values.
I want to populate the first 4 tables all at the same time; by providing
the grid which defines the 4th table, I want it to automatically back-
populate the first three tables. This should be do-able by creating a
RULE which says
CREATE RULE sometable_insert AS
ON INSERT TO sometable
WHERE EXISTS (SELECT sometable.uid WHERE sometable.field = new.field)
DO INSTEAD NOTHING ;
Then I can simply create an insert rule for my 4th table which inserts
a value into each of the first 3 tables.
Here is the example SQL. This all works, and if I insert data into
tailoring_combos_view one insert at a time, it works. The only time it
doesn't work is when I try to import multiple values at a time. It then
appears that the WHERE EXISTS statement in the above rule example is
not noticing that a value HAS been added.
DROP DATABASE daoc ;
CREATE DATABASE daoc ;
\c daoc
BEGIN ;
CREATE TABLE tailoring_types (
typeid SERIAL,
type VARCHAR(32) NOT NULL UNIQUE
) ;
CREATE TABLE tailoring_classes (
classid SERIAL,
class VARCHAR(32) NOT NULL UNIQUE
) ;
CREATE TABLE tailoring_places (
placeid SERIAL,
place VARCHAR(32) NOT NULL UNIQUE
) ;
CREATE TABLE tailoring_combos (
comboid SERIAL,
typeid INT NOT NULL
REFERENCES tailoring_types(typeid)
ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
classid INT NOT NULL
REFERENCES tailoring_classes(classid)
ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
placeid INT NOT NULL
REFERENCES tailoring_places(placeid)
ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
UNIQUE ( typeid, classid, placeid )
) ;
CREATE TABLE tailoring_prices (
comboid INT NOT NULL PRIMARY KEY
REFERENCES tailoring_combos(comboid)
ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
cost INT DEFAULT 0,
value INT DEFAULT 0
) ;
CREATE TABLE wishlist (
wishid SERIAL,
poster VARCHAR(16) NOT NULL,
posted DATETIME NOT NULL DEFAULT 'now',
wishtext TEXT NOT NULL,
votesaye INT DEFAULT 0,
votesnay INT DEFAULT 0
) ;
CREATE VIEW tailoring_combos_view AS
SELECT
t.type AS type,
cl.class AS class,
p.place AS place
FROM
tailoring_combos AS c,
tailoring_types AS t,
tailoring_classes AS cl,
tailoring_places AS p
WHERE
c.typeid = t.typeid AND
c.classid = cl.classid AND
c.placeid = p.placeid
ORDER BY
c.typeid, c.classid, c.placeid
;
CREATE RULE tailoring_types_insert AS
ON INSERT TO tailoring_types
WHERE EXISTS ( SELECT typeid FROM tailoring_types WHERE type = new.type )
DO INSTEAD NOTHING ;
CREATE RULE tailoring_classes_insert AS
ON INSERT TO tailoring_classes
WHERE EXISTS ( SELECT classid FROM tailoring_classes WHERE class = new.class )
DO INSTEAD NOTHING ;
CREATE RULE tailoring_places_insert AS
ON INSERT TO tailoring_places
WHERE EXISTS ( SELECT placeid FROM tailoring_places WHERE place = new.place )
DO INSTEAD NOTHING ;
CREATE RULE tailoring_combos_view_insert AS
ON INSERT
TO tailoring_combos_view
DO INSTEAD (
INSERT INTO tailoring_types (type) VALUES (new.type) ;
INSERT INTO tailoring_classes (class) VALUES (new.class) ;
INSERT INTO tailoring_places (place) VALUES (new.place) ;
INSERT INTO tailoring_combos (typeid, classid, placeid)
SELECT
t.typeid as typeid,
c.classid as classid,
p.placeid as placeid
FROM
tailoring_types AS t,
tailoring_classes AS c,
tailoring_places AS p
WHERE
t.type = new.type AND
c.class = new.class AND
p.place = new.place ;
) ;
END ;
BEGIN ;
CREATE TEMP TABLE combos ( type varchar(32), class varchar(32), place varchar(32) ) ;
COPY combos FROM stdin USING DELIMITERS ' ';
Cloth Woolen Hands
Cloth Linen Hands
Cloth Brocade Hands
Cloth Silk Hands
Robe Woolen Plain
Robe Woolen Dress
Robe Woolen Fancy
Robe Linen Plain
\.
INSERT INTO tailoring_combos_view (type,class,place) SELECT * FROM combos ;
END ;
BEGIN ;
/* The above fails with a duplicate key value. However, the next lines work */
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Woolen', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Linen', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Brocade', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Woolen', 'Head') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Linen', 'Head') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Brocade', 'Head') ;
INSERT INTO tailoring_combos_view VALUES ('Roman', 'Rawhide', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Roman', 'Tanned', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Roman', 'Cured', 'Hands') ;
END ;
--
You think Oedipus had problems? Adam was Eve's mother!
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
