From: Richard Huxton <dev@archonet.com>
To: Sarah Asmaels <sarah@spiesonline.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: writable joined view
Date: Tue, 25 Oct 2005 15:49:57 +0100
Message-ID: <435E4615.8080409@archonet.com> (raw)
In-Reply-To: <dj17je$7vu$1@news.spiesonline.net>
References: <dj17je$7vu$1@news.spiesonline.net>
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
Attachments:simple_joined_view_test_script.sql (text/x-sql, 2.4K) download
-- 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, sarah@spiesonline.net
Subject: Re: writable joined view
In-Reply-To: <435E4615.8080409@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox