SELECT * FROM shoelace ORDER BY sl_name;

sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm

------------+----------+------------+--------+----------+-----------

sl1 | 5 | black | 80 | cm | 80

sl2 | 6 | black | 100 | cm | 100

sl3 | 10 | black | 35 | inch | 88.9

sl4 | 8 | black | 40 | inch | 101.6

sl5 | 4 | brown | 1 | m | 100

sl6 | 20 | brown | 0.9 | m | 90

sl7 | 6 | brown | 60 | cm | 60

sl8 | 21 | brown | 40 | inch | 101.6

(8 rows)

SELECT * FROM shoelace_log ORDER BY sl_name;

sl_name | sl_avail | log_who | log_when

------------+----------+----------+----------

sl3 | 10 | Al Bundy | epoch

sl6 | 20 | Al Bundy | epoch

sl7 | 6 | Al Bundy | epoch

sl8 | 21 | Al Bundy | epoch

(4 rows)

CREATE VIEW shoelace_obsolete AS

SELECT * FROM shoelace WHERE NOT EXISTS

(SELECT shoename FROM shoe WHERE slcolor = sl_color);

CREATE VIEW shoelace_candelete AS

SELECT * FROM shoelace_obsolete WHERE sl_avail = 0;

insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0);

insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);

SELECT * FROM shoelace_obsolete;

sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm

------------+----------+------------+--------+----------+-----------

sl9 | 0 | pink | 35 | inch | 88.9

sl10 | 1000 | magenta | 40 | inch | 101.6

(2 rows)

SELECT * FROM shoelace_candelete;

sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm

------------+----------+------------+--------+----------+-----------

sl9 | 0 | pink | 35 | inch | 88.9

(1 row)

DELETE FROM shoelace WHERE EXISTS

(SELECT * FROM shoelace_candelete

WHERE sl_name = shoelace.sl_name);

SELECT * FROM shoelace ORDER BY sl_name;

sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm

------------+----------+------------+--------+----------+-----------

sl1 | 5 | black | 80 | cm | 80

sl10 | 1000 | magenta | 40 | inch | 101.6

sl2 | 6 | black | 100 | cm | 100

sl3 | 10 | black | 35 | inch | 88.9

sl4 | 8 | black | 40 | inch | 101.6

sl5 | 4 | brown | 1 | m | 100

sl6 | 20 | brown | 0.9 | m | 90

sl7 | 6 | brown | 60 | cm | 60

sl8 | 21 | brown | 40 | inch | 101.6

(9 rows)

SELECT * FROM shoe ORDER BY shoename;

shoename | sh_avail | slcolor | slminlen | slminlen_cm | slmaxlen | slmaxlen_cm | slunit

------------+----------+------------+----------+-------------+----------+-------------+----------

sh1 | 2 | black | 70 | 70 | 90 | 90 | cm

sh2 | 0 | black | 30 | 76.2 | 40 | 101.6 | inch

sh3 | 4 | brown | 50 | 50 | 65 | 65 | cm

sh4 | 3 | brown | 40 | 101.6 | 50 | 127 | inch

(4 rows)

SELECT count(*) FROM shoe;

count

-------

4

(1 row)

--

-- Simple test of qualified ON INSERT ... this did not work in 7.0 ...

--

create table foo (f1 int);

create table foo2 (f1 int);

create rule foorule as on insert to foo where f1 < 100

do instead nothing;

insert into foo values(1);

insert into foo values(1001);

select * from foo;

f1

------

1001

(1 row)

drop rule foorule;

-- this should fail because f1 is not exposed for unqualified reference:

create rule foorule as on insert to foo where f1 < 100

do instead insert into foo2 values (f1);

ERROR: Attribute 'f1' not found

-- this is the correct way:

create rule foorule as on insert to foo where f1 < 100

do instead insert into foo2 values (new.f1);

insert into foo values(2);

insert into foo values(100);

select * from foo;

f1

------

1001

100

(2 rows)

select * from foo2;

f1

----

2

(1 row)

drop rule foorule;

drop table foo;

drop table foo2;

--

-- Check that ruleutils are working

--

SELECT viewname, definition FROM pg_views ORDER BY viewname;

viewname | definition

--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);

pg_indexes | SELECT c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index x, pg_class c, pg_class i WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid));

pg_rules | SELECT c.relname AS tablename, r.rulename, pg_get_ruledef(r.rulename) AS definition FROM pg_rewrite r, pg_class c WHERE ((r.rulename !~ '^_RET'::text) AND (c.oid = r.ev_class));

pg_tables | SELECT c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM pg_class c WHERE (((c.relkind = 'r'::"char") OR (c.relkind = 's'::"char")) AND (NOT (EXISTS (SELECT pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND (pg_rewrite.ev_type = '1'::"char"))))));

pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usetrace, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil FROM pg_shadow;

pg_views | SELECT c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.relname) AS definition FROM pg_class c WHERE (c.relhasrules AND (EXISTS (SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (r.ev_type = '1'::"char")))));

rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1;

rtest_vcomp | SELECT x.part, (x.size * y.factor) AS size_in_cm FROM rtest_comp x, rtest_unitfact y WHERE (x.unit = y.unit);

rtest_vview1 | SELECT x.a, x.b FROM rtest_view1 x WHERE (0 < (SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a)));

rtest_vview2 | SELECT rtest_view1.a, rtest_view1.b FROM rtest_view1 WHERE rtest_view1.v;

rtest_vview3 | SELECT x.a, x.b FROM rtest_vview2 x WHERE (0 < (SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a)));

rtest_vview4 | SELECT x.a, x.b, count(y.a) AS refcount FROM rtest_view1 x, rtest_view2 y WHERE (x.a = y.a) GROUP BY x.a, x.b;

rtest_vview5 | SELECT rtest_view1.a, rtest_view1.b, rtest_viewfunc1(rtest_view1.a) AS refcount FROM rtest_view1;

shoe | SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE (sh.slunit = un.un_name);

shoe_ready | SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE (((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm)) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm));

shoelace | SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, (s.sl_len * u.un_fact) AS sl_len_cm FROM shoelace_data s, unit u WHERE (s.sl_unit = u.un_name);

shoelace_candelete | SELECT shoelace_obsolete.sl_name, shoelace_obsolete.sl_avail, shoelace_obsolete.sl_color, shoelace_obsolete.sl_len, shoelace_obsolete.sl_unit, shoelace_obsolete.sl_len_cm FROM shoelace_obsolete WHERE (shoelace_obsolete.sl_avail = 0);

shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));

street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);

toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;

(20 rows)

SELECT tablename, rulename, definition FROM pg_rules

ORDER BY tablename, rulename;

tablename | rulename | definition

---------------+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired '::bpchar, '$0.00'::money, old.salary);

rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired '::bpchar, new.salary, '$0.00'::money);

rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored '::bpchar, new.salary, old.salary);

rtest_nothn1 | rtest_nothn_r1 | CREATE RULE rtest_nothn_r1 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD SELECT 1;

rtest_nothn1 | rtest_nothn_r2 | CREATE RULE rtest_nothn_r2 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING;

rtest_nothn2 | rtest_nothn_r3 | CREATE RULE rtest_nothn_r3 AS ON INSERT TO rtest_nothn2 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) VALUES (new.a, new.b);

rtest_nothn2 | rtest_nothn_r4 | CREATE RULE rtest_nothn_r4 AS ON INSERT TO rtest_nothn2 DO INSTEAD NOTHING;

rtest_order1 | rtest_order_r1 | CREATE RULE rtest_order_r1 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 1 - this should run 3rd or 4th'::text);

rtest_order1 | rtest_order_r2 | CREATE RULE rtest_order_r2 AS ON INSERT TO rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 2 - this should run 1st'::text);

rtest_order1 | rtest_order_r3 | CREATE RULE rtest_order_r3 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 3 - this should run 3rd or 4th'::text);

rtest_order1 | rtest_order_r4 | CREATE RULE rtest_order_r4 AS ON INSERT TO rtest_order1 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::text), 'rule 4 - this should run 2nd'::text);

rtest_person | rtest_pers_del | CREATE RULE rtest_pers_del AS ON DELETE TO rtest_person DO DELETE FROM rtest_admin WHERE (rtest_admin.pname = old.pname);

rtest_person | rtest_pers_upd | CREATE RULE rtest_pers_upd AS ON UPDATE TO rtest_person DO UPDATE rtest_admin SET pname = new.pname WHERE (rtest_admin.pname = old.pname);

rtest_system | rtest_sys_del | CREATE RULE rtest_sys_del AS ON DELETE TO rtest_system DO (DELETE FROM rtest_interface WHERE (rtest_interface.sysname = old.sysname); DELETE FROM rtest_admin WHERE (rtest_admin.sysname = old.sysname); );

rtest_system | rtest_sys_upd | CREATE RULE rtest_sys_upd AS ON UPDATE TO rtest_system DO (UPDATE rtest_interface SET sysname = new.sysname WHERE (rtest_interface.sysname = old.sysname); UPDATE rtest_admin SET sysname = new.sysname WHERE (rtest_admin.sysname = old.sysname); );

rtest_t4 | rtest_t4_ins1 | CREATE RULE rtest_t4_ins1 AS ON INSERT TO rtest_t4 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD INSERT INTO rtest_t5 (a, b) VALUES (new.a, new.b);

rtest_t4 | rtest_t4_ins2 | CREATE RULE rtest_t4_ins2 AS ON INSERT TO rtest_t4 WHERE ((new.a >= 20) AND (new.a < 30)) DO INSERT INTO rtest_t6 (a, b) VALUES (new.a, new.b);

rtest_t5 | rtest_t5_ins | CREATE RULE rtest_t5_ins AS ON INSERT TO rtest_t5 WHERE (new.a > 15) DO INSERT INTO rtest_t7 (a, b) VALUES (new.a, new.b);

rtest_t6 | rtest_t6_ins | CREATE RULE rtest_t6_ins AS ON INSERT TO rtest_t6 WHERE (new.a > 25) DO INSTEAD INSERT INTO rtest_t8 (a, b) VALUES (new.a, new.b);

rtest_v1 | rtest_v1_del | CREATE RULE rtest_v1_del AS ON DELETE TO rtest_v1 DO INSTEAD DELETE FROM rtest_t1 WHERE (rtest_t1.a = old.a);

rtest_v1 | rtest_v1_ins | CREATE RULE rtest_v1_ins AS ON INSERT TO rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) VALUES (new.a, new.b);

rtest_v1 | rtest_v1_upd | CREATE RULE rtest_v1_upd AS ON UPDATE TO rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b WHERE (rtest_t1.a = old.a);

shoelace | shoelace_del | CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE (shoelace_data.sl_name = old.sl_name);

shoelace | shoelace_ins | CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit);

shoelace | shoelace_upd | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);

shoelace_data | log_shoelace | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, "timestamp"('epoch'::text));

shoelace_ok | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);

(27 rows)