Обсуждение: writable joined view

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.

Thank you,

Sarah

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.
> 
> Thank you,
> 
> Sarah

Is there any difference in multi or single table view? When you create a rule, 
you have access to NEW and/or OLD, which will contain all the fields your view 
has. You can then do something like this:

CREATE RULE insert_rule AS ON INSERT TO your_view DO INSTEAD (INSERT INTO table1 (name) VALUES (NEW.name);INSERT INTO
table2(favorite_color) VALUES (NEW.favorite_color);
 
);

Or is there something I'm not understanding about your request, or perhaps rules 
in general?

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;