Multiple Rules - an example

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