The interaction of rules with views can be complicated, so here's a
short sample file which illustrates the main points.
--
Richard Huxton
Archonet Ltd
=== BEGIN rule_test.sql ===
DROP TABLE foo CASCADE;
CREATE TABLE foo (a int4 PRIMARY KEY, b text, c text);
COPY foo FROM stdin;
1 aaa AAA
2 bbb AAA
3 ccc AAA
4 aaa BBB
5 bbb BBB
6 ccc BBB
\.
-- View selecting rows with odd value of "a"
CREATE VIEW foo_v AS SELECT * FROM foo WHERE (a % 2 = 1);
-- Alternate view selecting rows with b="bbb"
-- CREATE VIEW foo_v AS SELECT * FROM foo WHERE b='bbb';
CREATE RULE foo_v_upd0 AS ON UPDATE TO foo_v DO INSTEAD
UPDATE foo SET b = 'z' || NEW.b WHERE a = OLD.a;
CREATE RULE foo_v_upd1 AS ON UPDATE TO foo_v DO INSTEAD
UPDATE foo SET c = OLD.b WHERE a = OLD.a;
SELECT * FROM foo ORDER BY a;
UPDATE foo_v SET b='xxx';
SELECT * FROM foo ORDER BY a;
/*
1. Save this file as rule_test.sql and run from psql with \i rule_test.sql
2. Rename rule "foo_v_upd0" as "foo_v_upd2" and see what happens to the
order
of rule execution
3. Comment out the first view definition and uncomment the alternate
4. Rename "foo_v_upd2" back to "foo_v_upd0"
Note what happens when the first rule eliminates rows from the view
*/
=== END rule_test.sql ===