Sarah Asmaels wrote:
Hi!
I have one table referencing an object in another table through an ID,
and a view joining those tables on the ID. I want to create rules to
rewrite updates/deletes/inserts on the joined view to act on the real
tables. Can you give me some pointers? The documentation has only
examples for views depending on single tables.
I've attached a small example script that shows insert/update/delete on
a "joined" view.
--
Richard Huxton
Archonet Ltd
-- Rules on joined tables
-- Below are two tables: contact, contact_emails
-- Email addresses with a priority of 0 are considered "default"
-- Contacts can be either personal (PNL) or business (BUS)
--
BEGIN;
CREATE TABLE contacts (
id int4 NOT NULL UNIQUE,
full_name varchar(100),
con_type varchar(3) NOT NULL DEFAULT ('PNL') CHECK (con_type IN ('PNL','BUS')),
PRIMARY KEY (id)
);
CREATE TABLE contact_emails (
contact int4 NOT NULL REFERENCES contacts,
pri int2 CHECK (pri >= 0),
email varchar(100),
PRIMARY KEY (contact, pri)
);
COPY contacts (id,full_name,con_type) FROM stdin;
1 Aaron Aardvark PNL
2 Betty Bee PNL
3 Carl Cat PNL
4 Deputy Dawg BUS
5 Eric Elephant BUS
6 Fran Fish BUS
\.
COPY contact_emails (contact,pri,email) FROM stdin;
1 0 [EMAIL PROTECTED]
1 1 [EMAIL PROTECTED]
2 0 [EMAIL PROTECTED]
3 0 [EMAIL PROTECTED]
4 0 [EMAIL PROTECTED]
4 1 [EMAIL PROTECTED]
5 0 [EMAIL PROTECTED]
6 0 [EMAIL PROTECTED]
\.
COMMIT;
-- contact_defaults
-- A view that shows the default email for each contact.
-- There are rules that allow updating of the view.
-- Note how when deleting, we ignore "pri", but when updating we make sure it is set to 0
-- Obviously, we could have handled deleting emails through a FK cascade.
--
BEGIN;
CREATE VIEW contact_defaults AS
SELECT
c.id AS con_id,
c.full_name,
c.con_type,
e.email
FROM
contacts c,
contact_emails e
WHERE
c.id = e.contact
AND e.pri = 0
;
CREATE OR REPLACE RULE con_def_del AS ON DELETE TO contact_defaults
DO INSTEAD (
DELETE FROM contact_emails WHERE contact = OLD.con_id;
DELETE FROM contacts WHERE id = OLD.con_id;
);
CREATE OR REPLACE RULE con_def_upd AS ON UPDATE TO contact_defaults
DO INSTEAD (
UPDATE contact_emails SET email=NEW.email WHERE contact=OLD.con_id AND pri=0;
UPDATE contacts SET full_name=NEW.full_name, con_type=NEW.con_type WHERE id=OLD.con_id;
);
CREATE OR REPLACE RULE con_def_ins AS ON INSERT TO contact_defaults
DO INSTEAD (
INSERT INTO contacts (id,full_name,con_type) VALUES (NEW.con_id, NEW.full_name, NEW.con_type);
INSERT INTO contact_emails (contact,pri,email) VALUES (NEW.con_id, 0, NEW.email);
);
COMMIT;
-- Below are some queries to update the view and show what happens.
--
BEGIN;
SELECT * FROM contact_defaults ORDER BY con_id;
UPDATE contact_defaults SET con_type='BUS' WHERE con_id<4;
SELECT * FROM contact_defaults ORDER BY con_id;
UPDATE contact_defaults SET email=email || 'x' WHERE con_id>4;
SELECT * FROM contact_defaults ORDER BY con_id;
COMMIT;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
