Re: writable joined view - Mailing list pgsql-sql

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