-- 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	aaron@hotmail.com
1	1	aaron@aardvarks.com
2	0	betty@bees.com
3	0	carl@gmail.com
4	0	deputy@hotmail.com
4	1	deputy@gmail.com
5	0	eric@hotmail.com
6	0	fran@hotmail.com
\.
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;
