-- 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 ===
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]