Message-ID: <435E4615.8080409@archonet.com>
Date: Tue, 25 Oct 2005 15:49:57 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
MIME-Version: 1.0
To: Sarah Asmaels <sarah@spiesonline.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: writable joined view
References: <dj17je$7vu$1@news.spiesonline.net>
In-Reply-To: <dj17je$7vu$1@news.spiesonline.net>
Content-Type: multipart/mixed; boundary="------------090102070104030805080801"

This is a multi-part message in MIME format.
--------------090102070104030805080801
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------090102070104030805080801
Content-Type: text/x-sql;
 name="simple_joined_view_test_script.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="simple_joined_view_test_script.sql"

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

--------------090102070104030805080801--