[GENERAL] Multiple Rules - an example

[Richard Huxton]

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