From 5a68fd56cd1d4a0c6fb0930f5145ed39b92f759b Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Wed, 16 Sep 1998 14:35:37 +0000 Subject: Consolidate Jan's rules test into one file. Remove dependency on a specific Postgres user name in the results (Check result against CURRENT_USER with a boolean instead). --- src/test/regress/expected/rules.out | 673 +++++++++++++++++++++++++++ src/test/regress/expected/run_ruletest.out | 585 ----------------------- src/test/regress/expected/setup_ruletest.out | 88 ---- src/test/regress/sql/rules.sql | 406 ++++++++++++++++ src/test/regress/sql/run_ruletest.sql | 261 ----------- src/test/regress/sql/setup_ruletest.sql | 139 ------ src/test/regress/sql/tests | 3 +- 7 files changed, 1080 insertions(+), 1075 deletions(-) create mode 100644 src/test/regress/expected/rules.out delete mode 100644 src/test/regress/expected/run_ruletest.out delete mode 100644 src/test/regress/expected/setup_ruletest.out create mode 100644 src/test/regress/sql/rules.sql delete mode 100644 src/test/regress/sql/run_ruletest.sql delete mode 100644 src/test/regress/sql/setup_ruletest.sql diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out new file mode 100644 index 00000000000..447b1499faf --- /dev/null +++ b/src/test/regress/expected/rules.out @@ -0,0 +1,673 @@ +QUERY: create table rtest_t1 (a int4, b int4); +QUERY: create table rtest_t2 (a int4, b int4); +QUERY: create table rtest_t3 (a int4, b int4); +QUERY: create view rtest_v1 as select * from rtest_t1; +QUERY: create rule rtest_v1_ins as on insert to rtest_v1 do instead + insert into rtest_t1 values (new.a, new.b); +QUERY: create rule rtest_v1_upd as on update to rtest_v1 do instead + update rtest_t1 set a = new.a, b = new.b + where a = current.a; +QUERY: create rule rtest_v1_del as on delete to rtest_v1 do instead + delete from rtest_t1 where a = current.a; +QUERY: create table rtest_system (sysname text, sysdesc text); +QUERY: create table rtest_interface (sysname text, ifname text); +QUERY: create table rtest_person (pname text, pdesc text); +QUERY: create table rtest_admin (pname text, sysname text); +QUERY: create rule rtest_sys_upd1 as on update to rtest_system do + update rtest_interface set sysname = new.sysname + where sysname = current.sysname; +QUERY: create rule rtest_sys_upd2 as on update to rtest_system do + update rtest_admin set sysname = new.sysname + where sysname = current.sysname; +QUERY: create rule rtest_sys_del1 as on delete to rtest_system do + delete from rtest_interface where sysname = current.sysname; +QUERY: create rule rtest_sys_del2 as on delete to rtest_system do + delete from rtest_admin where sysname = current.sysname; +QUERY: create rule rtest_pers_upd as on update to rtest_person do + update rtest_admin set pname = new.pname where pname = current.pname; +QUERY: create rule rtest_pers_del as on delete to rtest_person do + delete from rtest_admin where pname = current.pname; +QUERY: create table rtest_emp (ename char(20), salary money); +QUERY: create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); +QUERY: create table rtest_empmass (ename char(20), salary money); +QUERY: create rule rtest_emp_ins as on insert to rtest_emp do + insert into rtest_emplog values (new.ename, current_user, + 'hired', new.salary, '0.00'); +QUERY: create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do + insert into rtest_emplog values (new.ename, current_user, + 'honored', new.salary, current.salary); +QUERY: create rule rtest_emp_del as on delete to rtest_emp do + insert into rtest_emplog values (current.ename, current_user, + 'fired', '0.00', current.salary); +QUERY: create table rtest_t4 (a int4, b text); +QUERY: create table rtest_t5 (a int4, b text); +QUERY: create table rtest_t6 (a int4, b text); +QUERY: create table rtest_t7 (a int4, b text); +QUERY: create table rtest_t8 (a int4, b text); +QUERY: create table rtest_t9 (a int4, b text); +QUERY: 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 values (new.a, new.b); +QUERY: create rule rtest_t4_ins2 as on insert to rtest_t4 + where new.a >= 20 and new.a < 30 do + insert into rtest_t6 values (new.a, new.b); +QUERY: create rule rtest_t5_ins as on insert to rtest_t5 + where new.a > 15 do + insert into rtest_t7 values (new.a, new.b); +QUERY: create rule rtest_t6_ins as on insert to rtest_t6 + where new.a > 25 do instead + insert into rtest_t8 values (new.a, new.b); +QUERY: create table rtest_order1 (a int4); +QUERY: create table rtest_order2 (a int4, b int4, c text); +QUERY: create sequence rtest_seq; +QUERY: create rule rtest_order_r3 as on insert to rtest_order1 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 3 - this should run 3rd or 4th'); +QUERY: create rule rtest_order_r4 as on insert to rtest_order1 + where a < 100 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 4 - this should run 2nd'); +QUERY: create rule rtest_order_r2 as on insert to rtest_order1 do + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 2 - this should run 1st'); +QUERY: create rule rtest_order_r1 as on insert to rtest_order1 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 1 - this should run 3rd or 4th'); +QUERY: create table rtest_nothn1 (a int4, b text); +QUERY: create table rtest_nothn2 (a int4, b text); +QUERY: create table rtest_nothn3 (a int4, b text); +QUERY: create table rtest_nothn4 (a int4, b text); +QUERY: create rule rtest_nothn_r1 as on insert to rtest_nothn1 + where new.a >= 10 and new.a < 20 do instead (select 1); +QUERY: create rule rtest_nothn_r2 as on insert to rtest_nothn1 + where new.a >= 30 and new.a < 40 do instead nothing; +QUERY: create rule rtest_nothn_r3 as on insert to rtest_nothn2 + where new.a >= 100 do instead + insert into rtest_nothn3 values (new.a, new.b); +QUERY: create rule rtest_nothn_r4 as on insert to rtest_nothn2 + do instead nothing; +QUERY: insert into rtest_t2 values (1, 21); +QUERY: insert into rtest_t2 values (2, 22); +QUERY: insert into rtest_t2 values (3, 23); +QUERY: insert into rtest_t3 values (1, 31); +QUERY: insert into rtest_t3 values (2, 32); +QUERY: insert into rtest_t3 values (3, 33); +QUERY: insert into rtest_t3 values (4, 34); +QUERY: insert into rtest_t3 values (5, 35); +QUERY: insert into rtest_v1 values (1, 11); +QUERY: insert into rtest_v1 values (2, 12); +QUERY: select * from rtest_v1; +a| b +-+-- +1|11 +2|12 +(2 rows) + +QUERY: delete from rtest_v1 where a = 1; +QUERY: select * from rtest_v1; +a| b +-+-- +2|12 +(1 row) + +QUERY: insert into rtest_v1 values (1, 11); +QUERY: delete from rtest_v1 where b = 12; +QUERY: select * from rtest_v1; +a| b +-+-- +1|11 +(1 row) + +QUERY: insert into rtest_v1 values (2, 12); +QUERY: insert into rtest_v1 values (2, 13); +QUERY: select * from rtest_v1; +a| b +-+-- +1|11 +2|12 +2|13 +(3 rows) + +QUERY: delete from rtest_v1 where b = 12; +QUERY: select * from rtest_v1; +** Remember the delete rule on rtest_v1: It says +** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a +** So this time both rows with a = 2 must get deleted +a| b +-+-- +1|11 +(1 row) + +QUERY: delete from rtest_v1; +QUERY: insert into rtest_v1 select * from rtest_t2; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: delete from rtest_v1; +QUERY: insert into rtest_v1 (b, a) select b, a from rtest_t2; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: insert into rtest_v1 (a) select a from rtest_t3; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +1| +2| +3| +4| +5| +(8 rows) + +QUERY: select * from rtest_v1 where b isnull; +a|b +-+- +1| +2| +3| +4| +5| +(5 rows) + +QUERY: update rtest_t1 set a = a + 10 where b isnull; +QUERY: delete from rtest_v1 where b isnull; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: update rtest_v1 set b = 42 where a = 2; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +3|23 +2|42 +(3 rows) + +QUERY: update rtest_v1 set b = 99 where b = 42; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +3|23 +2|99 +(3 rows) + +QUERY: update rtest_v1 set b = 88 where b < 50; +QUERY: select * from rtest_v1; +a| b +-+-- +2|99 +1|88 +3|88 +(3 rows) + +QUERY: delete from rtest_v1; +QUERY: insert into rtest_v1 select rtest_t2.a, rtest_t3.b where rtest_t2.a = rtest_t3.a; +QUERY: select * from rtest_v1; +a| b +-+-- +1|31 +2|32 +3|33 +(3 rows) + +QUERY: update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: insert into rtest_v1 select * from rtest_t3; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +1|31 +2|32 +3|33 +4|34 +5|35 +(8 rows) + +QUERY: update rtest_t1 set a = a + 10 where b > 30; +QUERY: select * from rtest_v1; + a| b +--+-- + 1|21 + 2|22 + 3|23 +11|31 +12|32 +13|33 +14|34 +15|35 +(8 rows) + +QUERY: update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b; +QUERY: select * from rtest_v1; + a| b +--+-- + 1|21 + 2|22 + 3|23 +21|31 +22|32 +23|33 +24|34 +25|35 +(8 rows) + +QUERY: insert into rtest_system values ('orion', 'Linux Jan Wieck'); +QUERY: insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); +QUERY: insert into rtest_system values ('neptun', 'Fileserver'); +QUERY: insert into rtest_interface values ('orion', 'eth0'); +QUERY: insert into rtest_interface values ('orion', 'eth1'); +QUERY: insert into rtest_interface values ('notjw', 'eth0'); +QUERY: insert into rtest_interface values ('neptun', 'eth0'); +QUERY: insert into rtest_person values ('jw', 'Jan Wieck'); +QUERY: insert into rtest_person values ('bm', 'Bruce Momjian'); +QUERY: insert into rtest_admin values ('jw', 'orion'); +QUERY: insert into rtest_admin values ('jw', 'notjw'); +QUERY: insert into rtest_admin values ('bm', 'neptun'); +QUERY: update rtest_system set sysname = 'pluto' where sysname = 'neptun'; +QUERY: select * from rtest_interface; +sysname|ifname +-------+------ +orion |eth0 +orion |eth1 +notjw |eth0 +pluto |eth0 +(4 rows) + +QUERY: select * from rtest_admin; +pname|sysname +-----+------- +jw |orion +jw |notjw +bm |pluto +(3 rows) + +QUERY: update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck'; +QUERY: select * from rtest_admin; +pname |sysname +------+------- +bm |pluto +jwieck|orion +jwieck|notjw +(3 rows) + +QUERY: delete from rtest_system where sysname = 'orion'; +QUERY: select * from rtest_interface; +sysname|ifname +-------+------ +notjw |eth0 +pluto |eth0 +(2 rows) + +QUERY: select * from rtest_admin; +pname |sysname +------+------- +bm |pluto +jwieck|notjw +(2 rows) + +QUERY: insert into rtest_emp values ('wiech', '5000.00'); +QUERY: insert into rtest_emp values ('gates', '80000.00'); +QUERY: update rtest_emp set ename = 'wiecx' where ename = 'wiech'; +QUERY: update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; +QUERY: update rtest_emp set salary = '7000.00' where ename = 'wieck'; +QUERY: delete from rtest_emp where ename = 'gates'; +QUERY: select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +ename |matches user|action |newsal |oldsal +--------------------+------------+----------+----------+---------- +wiech |t |hired |$5,000.00 |$0.00 +gates |t |hired |$80,000.00|$0.00 +wieck |t |honored |$6,000.00 |$5,000.00 +wieck |t |honored |$7,000.00 |$6,000.00 +gates |t |fired |$0.00 |$80,000.00 +(5 rows) + +QUERY: insert into rtest_empmass values ('meyer', '4000.00'); +QUERY: insert into rtest_empmass values ('maier', '5000.00'); +QUERY: insert into rtest_empmass values ('mayr', '6000.00'); +QUERY: insert into rtest_emp select * from rtest_empmass; +QUERY: select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +ename |matches user|action |newsal |oldsal +--------------------+------------+----------+----------+---------- +wiech |t |hired |$5,000.00 |$0.00 +gates |t |hired |$80,000.00|$0.00 +wieck |t |honored |$6,000.00 |$5,000.00 +wieck |t |honored |$7,000.00 |$6,000.00 +gates |t |fired |$0.00 |$80,000.00 +meyer |t |hired |$4,000.00 |$0.00 +maier |t |hired |$5,000.00 |$0.00 +mayr |t |hired |$6,000.00 |$0.00 +(8 rows) + +QUERY: update rtest_empmass set salary = salary + '1000.00'; +QUERY: update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename; +QUERY: select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +ename |matches user|action |newsal |oldsal +--------------------+------------+----------+----------+---------- +wiech |t |hired |$5,000.00 |$0.00 +gates |t |hired |$80,000.00|$0.00 +wieck |t |honored |$6,000.00 |$5,000.00 +wieck |t |honored |$7,000.00 |$6,000.00 +gates |t |fired |$0.00 |$80,000.00 +meyer |t |hired |$4,000.00 |$0.00 +maier |t |hired |$5,000.00 |$0.00 +mayr |t |hired |$6,000.00 |$0.00 +maier |t |honored |$6,000.00 |$5,000.00 +mayr |t |honored |$7,000.00 |$6,000.00 +meyer |t |honored |$5,000.00 |$4,000.00 +(11 rows) + +QUERY: delete from rtest_emp where ename = rtest_empmass.ename; +QUERY: select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +ename |matches user|action |newsal |oldsal +--------------------+------------+----------+----------+---------- +wiech |t |hired |$5,000.00 |$0.00 +gates |t |hired |$80,000.00|$0.00 +wieck |t |honored |$6,000.00 |$5,000.00 +wieck |t |honored |$7,000.00 |$6,000.00 +gates |t |fired |$0.00 |$80,000.00 +meyer |t |hired |$4,000.00 |$0.00 +maier |t |hired |$5,000.00 |$0.00 +mayr |t |hired |$6,000.00 |$0.00 +maier |t |honored |$6,000.00 |$5,000.00 +mayr |t |honored |$7,000.00 |$6,000.00 +meyer |t |honored |$5,000.00 |$4,000.00 +maier |t |fired |$0.00 |$6,000.00 +mayr |t |fired |$0.00 |$7,000.00 +meyer |t |fired |$0.00 |$5,000.00 +(14 rows) + +QUERY: insert into rtest_t4 values (1, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 values (2, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 values (10, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t4 values (15, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); +QUERY: insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); +QUERY: insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t4 values (30, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 values (40, 'Record should go to rtest_t4'); +QUERY: select * from rtest_t4; + a|b +--+----------------------------------- + 1|Record should go to rtest_t4 + 2|Record should go to rtest_t4 +20|Record should go to rtest_t4 and t6 +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +30|Record should go to rtest_t4 +40|Record should go to rtest_t4 +(7 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; + a|b +--+----------------------------------- +20|Record should go to rtest_t4 and t6 +(1 row) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; + a|b +--+----------------------------------- +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(2 rows) + +QUERY: delete from rtest_t4; +QUERY: delete from rtest_t5; +QUERY: delete from rtest_t6; +QUERY: delete from rtest_t7; +QUERY: delete from rtest_t8; +QUERY: insert into rtest_t9 values (1, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t9 values (2, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t9 values (10, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t9 values (15, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); +QUERY: insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); +QUERY: insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t9 values (30, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t9 values (40, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 select * from rtest_t9 where a < 20; +QUERY: select * from rtest_t4; +a|b +-+---------------------------- +1|Record should go to rtest_t4 +2|Record should go to rtest_t4 +(2 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; +a|b +-+- +(0 rows) + +QUERY: insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; +QUERY: select * from rtest_t4; + a|b +--+----------------------------------- + 1|Record should go to rtest_t4 + 2|Record should go to rtest_t4 +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(4 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; + a|b +--+----------------------------------- +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(2 rows) + +QUERY: insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); +QUERY: select * from rtest_t4; + a|b +--+----------------------------------- + 1|Record should go to rtest_t4 + 2|Record should go to rtest_t4 +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +21|Record should go to rtest_t4 and t6 +31|Record should go to rtest_t4 +41|Record should go to rtest_t4 +(7 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; + a|b +--+----------------------------------- +21|Record should go to rtest_t4 and t6 +(1 row) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; + a|b +--+----------------------------------- +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(2 rows) + +QUERY: insert into rtest_order1 values (1); +QUERY: select * from rtest_order2; +a|b|c +-+-+----------------------------------- +1|1|rule 2 - this should run 1st +1|2|rule 4 - this should run 2nd +1|3|rule 3 - this should run 3rd or 4th +1|4|rule 1 - this should run 3rd or 4th +(4 rows) + +QUERY: insert into rtest_nothn1 values (1, 'want this'); +QUERY: insert into rtest_nothn1 values (2, 'want this'); +QUERY: insert into rtest_nothn1 values (10, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (19, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (20, 'want this'); +QUERY: insert into rtest_nothn1 values (29, 'want this'); +QUERY: insert into rtest_nothn1 values (30, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (39, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (40, 'want this'); +QUERY: insert into rtest_nothn1 values (50, 'want this'); +QUERY: insert into rtest_nothn1 values (60, 'want this'); +QUERY: select * from rtest_nothn1; + a|b +--+--------- + 1|want this + 2|want this +20|want this +29|want this +40|want this +50|want this +60|want this +(7 rows) + +QUERY: insert into rtest_nothn2 values (10, 'too small'); +QUERY: insert into rtest_nothn2 values (50, 'too small'); +QUERY: insert into rtest_nothn2 values (100, 'OK'); +QUERY: insert into rtest_nothn2 values (200, 'OK'); +QUERY: select * from rtest_nothn2; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_nothn3; + a|b +---+-- +100|OK +200|OK +(2 rows) + +QUERY: delete from rtest_nothn1; +QUERY: delete from rtest_nothn2; +QUERY: delete from rtest_nothn3; +QUERY: insert into rtest_nothn4 values (1, 'want this'); +QUERY: insert into rtest_nothn4 values (2, 'want this'); +QUERY: insert into rtest_nothn4 values (10, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (19, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (20, 'want this'); +QUERY: insert into rtest_nothn4 values (29, 'want this'); +QUERY: insert into rtest_nothn4 values (30, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (39, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (40, 'want this'); +QUERY: insert into rtest_nothn4 values (50, 'want this'); +QUERY: insert into rtest_nothn4 values (60, 'want this'); +QUERY: insert into rtest_nothn1 select * from rtest_nothn4; +QUERY: select * from rtest_nothn1; + a|b +--+--------- + 1|want this + 2|want this +20|want this +29|want this +40|want this +50|want this +60|want this +(7 rows) + +QUERY: delete from rtest_nothn4; +QUERY: insert into rtest_nothn4 values (10, 'too small'); +QUERY: insert into rtest_nothn4 values (50, 'too small'); +QUERY: insert into rtest_nothn4 values (100, 'OK'); +QUERY: insert into rtest_nothn4 values (200, 'OK'); +QUERY: insert into rtest_nothn2 select * from rtest_nothn4; +QUERY: select * from rtest_nothn2; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_nothn3; + a|b +---+-- +100|OK +200|OK +(2 rows) + diff --git a/src/test/regress/expected/run_ruletest.out b/src/test/regress/expected/run_ruletest.out deleted file mode 100644 index 0421ad47b5d..00000000000 --- a/src/test/regress/expected/run_ruletest.out +++ /dev/null @@ -1,585 +0,0 @@ -QUERY: insert into rtest_t2 values (1, 21); -QUERY: insert into rtest_t2 values (2, 22); -QUERY: insert into rtest_t2 values (3, 23); -QUERY: insert into rtest_t3 values (1, 31); -QUERY: insert into rtest_t3 values (2, 32); -QUERY: insert into rtest_t3 values (3, 33); -QUERY: insert into rtest_t3 values (4, 34); -QUERY: insert into rtest_t3 values (5, 35); -QUERY: insert into rtest_v1 values (1, 11); -QUERY: insert into rtest_v1 values (2, 12); -QUERY: select * from rtest_v1; -a| b --+-- -1|11 -2|12 -(2 rows) - -QUERY: delete from rtest_v1 where a = 1; -QUERY: select * from rtest_v1; -a| b --+-- -2|12 -(1 row) - -QUERY: insert into rtest_v1 values (1, 11); -QUERY: delete from rtest_v1 where b = 12; -QUERY: select * from rtest_v1; -a| b --+-- -1|11 -(1 row) - -QUERY: insert into rtest_v1 values (2, 12); -QUERY: insert into rtest_v1 values (2, 13); -QUERY: select * from rtest_v1; -a| b --+-- -1|11 -2|12 -2|13 -(3 rows) - -QUERY: delete from rtest_v1 where b = 12; -QUERY: select * from rtest_v1; -** Remember the delete rule on rtest_v1: It says -** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a -** So this time both rows with a = 2 must get deleted -a| b --+-- -1|11 -(1 row) - -QUERY: delete from rtest_v1; -QUERY: insert into rtest_v1 select * from rtest_t2; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -2|22 -3|23 -(3 rows) - -QUERY: delete from rtest_v1; -QUERY: insert into rtest_v1 (b, a) select b, a from rtest_t2; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -2|22 -3|23 -(3 rows) - -QUERY: insert into rtest_v1 (a) select a from rtest_t3; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -2|22 -3|23 -1| -2| -3| -4| -5| -(8 rows) - -QUERY: select * from rtest_v1 where b isnull; -a|b --+- -1| -2| -3| -4| -5| -(5 rows) - -QUERY: update rtest_t1 set a = a + 10 where b isnull; -QUERY: delete from rtest_v1 where b isnull; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -2|22 -3|23 -(3 rows) - -QUERY: update rtest_v1 set b = 42 where a = 2; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -3|23 -2|42 -(3 rows) - -QUERY: update rtest_v1 set b = 99 where b = 42; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -3|23 -2|99 -(3 rows) - -QUERY: update rtest_v1 set b = 88 where b < 50; -QUERY: select * from rtest_v1; -a| b --+-- -2|99 -1|88 -3|88 -(3 rows) - -QUERY: delete from rtest_v1; -QUERY: insert into rtest_v1 select rtest_t2.a, rtest_t3.b where rtest_t2.a = rtest_t3.a; -QUERY: select * from rtest_v1; -a| b --+-- -1|31 -2|32 -3|33 -(3 rows) - -QUERY: update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -2|22 -3|23 -(3 rows) - -QUERY: insert into rtest_v1 select * from rtest_t3; -QUERY: select * from rtest_v1; -a| b --+-- -1|21 -2|22 -3|23 -1|31 -2|32 -3|33 -4|34 -5|35 -(8 rows) - -QUERY: update rtest_t1 set a = a + 10 where b > 30; -QUERY: select * from rtest_v1; - a| b ---+-- - 1|21 - 2|22 - 3|23 -11|31 -12|32 -13|33 -14|34 -15|35 -(8 rows) - -QUERY: update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b; -QUERY: select * from rtest_v1; - a| b ---+-- - 1|21 - 2|22 - 3|23 -21|31 -22|32 -23|33 -24|34 -25|35 -(8 rows) - -QUERY: insert into rtest_system values ('orion', 'Linux Jan Wieck'); -QUERY: insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); -QUERY: insert into rtest_system values ('neptun', 'Fileserver'); -QUERY: insert into rtest_interface values ('orion', 'eth0'); -QUERY: insert into rtest_interface values ('orion', 'eth1'); -QUERY: insert into rtest_interface values ('notjw', 'eth0'); -QUERY: insert into rtest_interface values ('neptun', 'eth0'); -QUERY: insert into rtest_person values ('jw', 'Jan Wieck'); -QUERY: insert into rtest_person values ('bm', 'Bruce Momjian'); -QUERY: insert into rtest_admin values ('jw', 'orion'); -QUERY: insert into rtest_admin values ('jw', 'notjw'); -QUERY: insert into rtest_admin values ('bm', 'neptun'); -QUERY: update rtest_system set sysname = 'pluto' where sysname = 'neptun'; -QUERY: select * from rtest_interface; -sysname|ifname --------+------ -orion |eth0 -orion |eth1 -notjw |eth0 -pluto |eth0 -(4 rows) - -QUERY: select * from rtest_admin; -pname|sysname ------+------- -jw |orion -jw |notjw -bm |pluto -(3 rows) - -QUERY: update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck'; -QUERY: select * from rtest_admin; -pname |sysname -------+------- -bm |pluto -jwieck|orion -jwieck|notjw -(3 rows) - -QUERY: delete from rtest_system where sysname = 'orion'; -QUERY: select * from rtest_interface; -sysname|ifname --------+------ -notjw |eth0 -pluto |eth0 -(2 rows) - -QUERY: select * from rtest_admin; -pname |sysname -------+------- -bm |pluto -jwieck|notjw -(2 rows) - -QUERY: insert into rtest_emp values ('wiech', '5000.00'); -QUERY: insert into rtest_emp values ('gates', '80000.00'); -QUERY: update rtest_emp set ename = 'wiecx' where ename = 'wiech'; -QUERY: update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; -QUERY: update rtest_emp set salary = '7000.00' where ename = 'wieck'; -QUERY: delete from rtest_emp where ename = 'gates'; -QUERY: select * from rtest_emplog; -ename |who |action |newsal |oldsal ---------------------+-----+----------+----------+---------- -wiech |pgsql|hired |$5,000.00 |$0.00 -gates |pgsql|hired |$80,000.00|$0.00 -wieck |pgsql|honored |$6,000.00 |$5,000.00 -wieck |pgsql|honored |$7,000.00 |$6,000.00 -gates |pgsql|fired |$0.00 |$80,000.00 -(5 rows) - -QUERY: insert into rtest_empmass values ('meyer', '4000.00'); -QUERY: insert into rtest_empmass values ('maier', '5000.00'); -QUERY: insert into rtest_empmass values ('mayr', '6000.00'); -QUERY: insert into rtest_emp select * from rtest_empmass; -QUERY: select * from rtest_emplog; -ename |who |action |newsal |oldsal ---------------------+-----+----------+----------+---------- -wiech |pgsql|hired |$5,000.00 |$0.00 -gates |pgsql|hired |$80,000.00|$0.00 -wieck |pgsql|honored |$6,000.00 |$5,000.00 -wieck |pgsql|honored |$7,000.00 |$6,000.00 -gates |pgsql|fired |$0.00 |$80,000.00 -meyer |pgsql|hired |$4,000.00 |$0.00 -maier |pgsql|hired |$5,000.00 |$0.00 -mayr |pgsql|hired |$6,000.00 |$0.00 -(8 rows) - -QUERY: update rtest_empmass set salary = salary + '1000.00'; -QUERY: update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename; -QUERY: select * from rtest_emplog; -ename |who |action |newsal |oldsal ---------------------+-----+----------+----------+---------- -wiech |pgsql|hired |$5,000.00 |$0.00 -gates |pgsql|hired |$80,000.00|$0.00 -wieck |pgsql|honored |$6,000.00 |$5,000.00 -wieck |pgsql|honored |$7,000.00 |$6,000.00 -gates |pgsql|fired |$0.00 |$80,000.00 -meyer |pgsql|hired |$4,000.00 |$0.00 -maier |pgsql|hired |$5,000.00 |$0.00 -mayr |pgsql|hired |$6,000.00 |$0.00 -maier |pgsql|honored |$6,000.00 |$5,000.00 -mayr |pgsql|honored |$7,000.00 |$6,000.00 -meyer |pgsql|honored |$5,000.00 |$4,000.00 -(11 rows) - -QUERY: delete from rtest_emp where ename = rtest_empmass.ename; -QUERY: select * from rtest_emplog; -ename |who |action |newsal |oldsal ---------------------+-----+----------+----------+---------- -wiech |pgsql|hired |$5,000.00 |$0.00 -gates |pgsql|hired |$80,000.00|$0.00 -wieck |pgsql|honored |$6,000.00 |$5,000.00 -wieck |pgsql|honored |$7,000.00 |$6,000.00 -gates |pgsql|fired |$0.00 |$80,000.00 -meyer |pgsql|hired |$4,000.00 |$0.00 -maier |pgsql|hired |$5,000.00 |$0.00 -mayr |pgsql|hired |$6,000.00 |$0.00 -maier |pgsql|honored |$6,000.00 |$5,000.00 -mayr |pgsql|honored |$7,000.00 |$6,000.00 -meyer |pgsql|honored |$5,000.00 |$4,000.00 -maier |pgsql|fired |$0.00 |$6,000.00 -mayr |pgsql|fired |$0.00 |$7,000.00 -meyer |pgsql|fired |$0.00 |$5,000.00 -(14 rows) - -QUERY: insert into rtest_t4 values (1, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t4 values (2, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t4 values (10, 'Record should go to rtest_t5'); -QUERY: insert into rtest_t4 values (15, 'Record should go to rtest_t5'); -QUERY: insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); -QUERY: insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); -QUERY: insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); -QUERY: insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); -QUERY: insert into rtest_t4 values (30, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t4 values (40, 'Record should go to rtest_t4'); -QUERY: select * from rtest_t4; - a|b ---+----------------------------------- - 1|Record should go to rtest_t4 - 2|Record should go to rtest_t4 -20|Record should go to rtest_t4 and t6 -26|Record should go to rtest_t4 and t8 -28|Record should go to rtest_t4 and t8 -30|Record should go to rtest_t4 -40|Record should go to rtest_t4 -(7 rows) - -QUERY: select * from rtest_t5; - a|b ---+----------------------------------- -10|Record should go to rtest_t5 -15|Record should go to rtest_t5 -19|Record should go to rtest_t5 and t7 -(3 rows) - -QUERY: select * from rtest_t6; - a|b ---+----------------------------------- -20|Record should go to rtest_t4 and t6 -(1 row) - -QUERY: select * from rtest_t7; - a|b ---+----------------------------------- -19|Record should go to rtest_t5 and t7 -(1 row) - -QUERY: select * from rtest_t8; - a|b ---+----------------------------------- -26|Record should go to rtest_t4 and t8 -28|Record should go to rtest_t4 and t8 -(2 rows) - -QUERY: delete from rtest_t4; -QUERY: delete from rtest_t5; -QUERY: delete from rtest_t6; -QUERY: delete from rtest_t7; -QUERY: delete from rtest_t8; -QUERY: insert into rtest_t9 values (1, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t9 values (2, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t9 values (10, 'Record should go to rtest_t5'); -QUERY: insert into rtest_t9 values (15, 'Record should go to rtest_t5'); -QUERY: insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); -QUERY: insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); -QUERY: insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); -QUERY: insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); -QUERY: insert into rtest_t9 values (30, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t9 values (40, 'Record should go to rtest_t4'); -QUERY: insert into rtest_t4 select * from rtest_t9 where a < 20; -QUERY: select * from rtest_t4; -a|b --+---------------------------- -1|Record should go to rtest_t4 -2|Record should go to rtest_t4 -(2 rows) - -QUERY: select * from rtest_t5; - a|b ---+----------------------------------- -10|Record should go to rtest_t5 -15|Record should go to rtest_t5 -19|Record should go to rtest_t5 and t7 -(3 rows) - -QUERY: select * from rtest_t6; -a|b --+- -(0 rows) - -QUERY: select * from rtest_t7; - a|b ---+----------------------------------- -19|Record should go to rtest_t5 and t7 -(1 row) - -QUERY: select * from rtest_t8; -a|b --+- -(0 rows) - -QUERY: insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; -QUERY: select * from rtest_t4; - a|b ---+----------------------------------- - 1|Record should go to rtest_t4 - 2|Record should go to rtest_t4 -26|Record should go to rtest_t4 and t8 -28|Record should go to rtest_t4 and t8 -(4 rows) - -QUERY: select * from rtest_t5; - a|b ---+----------------------------------- -10|Record should go to rtest_t5 -15|Record should go to rtest_t5 -19|Record should go to rtest_t5 and t7 -(3 rows) - -QUERY: select * from rtest_t6; -a|b --+- -(0 rows) - -QUERY: select * from rtest_t7; - a|b ---+----------------------------------- -19|Record should go to rtest_t5 and t7 -(1 row) - -QUERY: select * from rtest_t8; - a|b ---+----------------------------------- -26|Record should go to rtest_t4 and t8 -28|Record should go to rtest_t4 and t8 -(2 rows) - -QUERY: insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); -QUERY: select * from rtest_t4; - a|b ---+----------------------------------- - 1|Record should go to rtest_t4 - 2|Record should go to rtest_t4 -26|Record should go to rtest_t4 and t8 -28|Record should go to rtest_t4 and t8 -21|Record should go to rtest_t4 and t6 -31|Record should go to rtest_t4 -41|Record should go to rtest_t4 -(7 rows) - -QUERY: select * from rtest_t5; - a|b ---+----------------------------------- -10|Record should go to rtest_t5 -15|Record should go to rtest_t5 -19|Record should go to rtest_t5 and t7 -(3 rows) - -QUERY: select * from rtest_t6; - a|b ---+----------------------------------- -21|Record should go to rtest_t4 and t6 -(1 row) - -QUERY: select * from rtest_t7; - a|b ---+----------------------------------- -19|Record should go to rtest_t5 and t7 -(1 row) - -QUERY: select * from rtest_t8; - a|b ---+----------------------------------- -26|Record should go to rtest_t4 and t8 -28|Record should go to rtest_t4 and t8 -(2 rows) - -QUERY: insert into rtest_order1 values (1); -QUERY: select * from rtest_order2; -a|b|c --+-+----------------------------------- -1|1|rule 2 - this should run 1st -1|2|rule 4 - this should run 2nd -1|3|rule 3 - this should run 3rd or 4th -1|4|rule 1 - this should run 3rd or 4th -(4 rows) - -QUERY: insert into rtest_nothn1 values (1, 'want this'); -QUERY: insert into rtest_nothn1 values (2, 'want this'); -QUERY: insert into rtest_nothn1 values (10, 'don''t want this'); -QUERY: insert into rtest_nothn1 values (19, 'don''t want this'); -QUERY: insert into rtest_nothn1 values (20, 'want this'); -QUERY: insert into rtest_nothn1 values (29, 'want this'); -QUERY: insert into rtest_nothn1 values (30, 'don''t want this'); -QUERY: insert into rtest_nothn1 values (39, 'don''t want this'); -QUERY: insert into rtest_nothn1 values (40, 'want this'); -QUERY: insert into rtest_nothn1 values (50, 'want this'); -QUERY: insert into rtest_nothn1 values (60, 'want this'); -QUERY: select * from rtest_nothn1; - a|b ---+--------- - 1|want this - 2|want this -20|want this -29|want this -40|want this -50|want this -60|want this -(7 rows) - -QUERY: insert into rtest_nothn2 values (10, 'too small'); -QUERY: insert into rtest_nothn2 values (50, 'too small'); -QUERY: insert into rtest_nothn2 values (100, 'OK'); -QUERY: insert into rtest_nothn2 values (200, 'OK'); -QUERY: select * from rtest_nothn2; -a|b --+- -(0 rows) - -QUERY: select * from rtest_nothn3; - a|b ----+-- -100|OK -200|OK -(2 rows) - -QUERY: delete from rtest_nothn1; -QUERY: delete from rtest_nothn2; -QUERY: delete from rtest_nothn3; -QUERY: insert into rtest_nothn4 values (1, 'want this'); -QUERY: insert into rtest_nothn4 values (2, 'want this'); -QUERY: insert into rtest_nothn4 values (10, 'don''t want this'); -QUERY: insert into rtest_nothn4 values (19, 'don''t want this'); -QUERY: insert into rtest_nothn4 values (20, 'want this'); -QUERY: insert into rtest_nothn4 values (29, 'want this'); -QUERY: insert into rtest_nothn4 values (30, 'don''t want this'); -QUERY: insert into rtest_nothn4 values (39, 'don''t want this'); -QUERY: insert into rtest_nothn4 values (40, 'want this'); -QUERY: insert into rtest_nothn4 values (50, 'want this'); -QUERY: insert into rtest_nothn4 values (60, 'want this'); -QUERY: insert into rtest_nothn1 select * from rtest_nothn4; -QUERY: select * from rtest_nothn1; - a|b ---+--------- - 1|want this - 2|want this -20|want this -29|want this -40|want this -50|want this -60|want this -(7 rows) - -QUERY: delete from rtest_nothn4; -QUERY: insert into rtest_nothn4 values (10, 'too small'); -QUERY: insert into rtest_nothn4 values (50, 'too small'); -QUERY: insert into rtest_nothn4 values (100, 'OK'); -QUERY: insert into rtest_nothn4 values (200, 'OK'); -QUERY: insert into rtest_nothn2 select * from rtest_nothn4; -QUERY: select * from rtest_nothn2; -a|b --+- -(0 rows) - -QUERY: select * from rtest_nothn3; - a|b ----+-- -100|OK -200|OK -(2 rows) - diff --git a/src/test/regress/expected/setup_ruletest.out b/src/test/regress/expected/setup_ruletest.out deleted file mode 100644 index 9cd3c09026a..00000000000 --- a/src/test/regress/expected/setup_ruletest.out +++ /dev/null @@ -1,88 +0,0 @@ -QUERY: create table rtest_t1 (a int4, b int4); -QUERY: create table rtest_t2 (a int4, b int4); -QUERY: create table rtest_t3 (a int4, b int4); -QUERY: create view rtest_v1 as select * from rtest_t1; -QUERY: create rule rtest_v1_ins as on insert to rtest_v1 do instead - insert into rtest_t1 values (new.a, new.b); -QUERY: create rule rtest_v1_upd as on update to rtest_v1 do instead - update rtest_t1 set a = new.a, b = new.b - where a = current.a; -QUERY: create rule rtest_v1_del as on delete to rtest_v1 do instead - delete from rtest_t1 where a = current.a; -QUERY: create table rtest_system (sysname text, sysdesc text); -QUERY: create table rtest_interface (sysname text, ifname text); -QUERY: create table rtest_person (pname text, pdesc text); -QUERY: create table rtest_admin (pname text, sysname text); -QUERY: create rule rtest_sys_upd1 as on update to rtest_system do - update rtest_interface set sysname = new.sysname - where sysname = current.sysname; -QUERY: create rule rtest_sys_upd2 as on update to rtest_system do - update rtest_admin set sysname = new.sysname - where sysname = current.sysname; -QUERY: create rule rtest_sys_del1 as on delete to rtest_system do - delete from rtest_interface where sysname = current.sysname; -QUERY: create rule rtest_sys_del2 as on delete to rtest_system do - delete from rtest_admin where sysname = current.sysname; -QUERY: create rule rtest_pers_upd as on update to rtest_person do - update rtest_admin set pname = new.pname where pname = current.pname; -QUERY: create rule rtest_pers_del as on delete to rtest_person do - delete from rtest_admin where pname = current.pname; -QUERY: create table rtest_emp (ename char(20), salary money); -QUERY: create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); -QUERY: create table rtest_empmass (ename char(20), salary money); -QUERY: create rule rtest_emp_ins as on insert to rtest_emp do - insert into rtest_emplog values (new.ename, getpgusername(), - 'hired', new.salary, '0.00'); -QUERY: create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do - insert into rtest_emplog values (new.ename, getpgusername(), - 'honored', new.salary, current.salary); -QUERY: create rule rtest_emp_del as on delete to rtest_emp do - insert into rtest_emplog values (current.ename, getpgusername(), - 'fired', '0.00', current.salary); -QUERY: create table rtest_t4 (a int4, b text); -QUERY: create table rtest_t5 (a int4, b text); -QUERY: create table rtest_t6 (a int4, b text); -QUERY: create table rtest_t7 (a int4, b text); -QUERY: create table rtest_t8 (a int4, b text); -QUERY: create table rtest_t9 (a int4, b text); -QUERY: 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 values (new.a, new.b); -QUERY: create rule rtest_t4_ins2 as on insert to rtest_t4 - where new.a >= 20 and new.a < 30 do - insert into rtest_t6 values (new.a, new.b); -QUERY: create rule rtest_t5_ins as on insert to rtest_t5 - where new.a > 15 do - insert into rtest_t7 values (new.a, new.b); -QUERY: create rule rtest_t6_ins as on insert to rtest_t6 - where new.a > 25 do instead - insert into rtest_t8 values (new.a, new.b); -QUERY: create table rtest_order1 (a int4); -QUERY: create table rtest_order2 (a int4, b int4, c text); -QUERY: create sequence rtest_seq; -QUERY: create rule rtest_order_r3 as on insert to rtest_order1 do instead - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 3 - this should run 3rd or 4th'); -QUERY: create rule rtest_order_r4 as on insert to rtest_order1 - where a < 100 do instead - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 4 - this should run 2nd'); -QUERY: create rule rtest_order_r2 as on insert to rtest_order1 do - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 2 - this should run 1st'); -QUERY: create rule rtest_order_r1 as on insert to rtest_order1 do instead - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 1 - this should run 3rd or 4th'); -QUERY: create table rtest_nothn1 (a int4, b text); -QUERY: create table rtest_nothn2 (a int4, b text); -QUERY: create table rtest_nothn3 (a int4, b text); -QUERY: create table rtest_nothn4 (a int4, b text); -QUERY: create rule rtest_nothn_r1 as on insert to rtest_nothn1 - where new.a >= 10 and new.a < 20 do instead (select 1); -QUERY: create rule rtest_nothn_r2 as on insert to rtest_nothn1 - where new.a >= 30 and new.a < 40 do instead nothing; -QUERY: create rule rtest_nothn_r3 as on insert to rtest_nothn2 - where new.a >= 100 do instead - insert into rtest_nothn3 values (new.a, new.b); -QUERY: create rule rtest_nothn_r4 as on insert to rtest_nothn2 - do instead nothing; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql new file mode 100644 index 00000000000..6ca18775863 --- /dev/null +++ b/src/test/regress/sql/rules.sql @@ -0,0 +1,406 @@ +-- +-- rules.sql +-- From Jan's original setup_ruletest.sql and run_ruletest.sql +-- - thomas 1998-09-13 +-- + +-- +-- Tables and rules for the view test +-- +create table rtest_t1 (a int4, b int4); +create table rtest_t2 (a int4, b int4); +create table rtest_t3 (a int4, b int4); + +create view rtest_v1 as select * from rtest_t1; +create rule rtest_v1_ins as on insert to rtest_v1 do instead + insert into rtest_t1 values (new.a, new.b); +create rule rtest_v1_upd as on update to rtest_v1 do instead + update rtest_t1 set a = new.a, b = new.b + where a = current.a; +create rule rtest_v1_del as on delete to rtest_v1 do instead + delete from rtest_t1 where a = current.a; + +-- +-- Tables and rules for the constraint update/delete test +-- +-- Note: +-- psql prevents from putting colons into brackets as +-- required for multi action rules. So we define single +-- rules for each action required for now +-- +create table rtest_system (sysname text, sysdesc text); +create table rtest_interface (sysname text, ifname text); +create table rtest_person (pname text, pdesc text); +create table rtest_admin (pname text, sysname text); + +create rule rtest_sys_upd1 as on update to rtest_system do + update rtest_interface set sysname = new.sysname + where sysname = current.sysname; +create rule rtest_sys_upd2 as on update to rtest_system do + update rtest_admin set sysname = new.sysname + where sysname = current.sysname; + +create rule rtest_sys_del1 as on delete to rtest_system do + delete from rtest_interface where sysname = current.sysname; +create rule rtest_sys_del2 as on delete to rtest_system do + delete from rtest_admin where sysname = current.sysname; + +create rule rtest_pers_upd as on update to rtest_person do + update rtest_admin set pname = new.pname where pname = current.pname; + +create rule rtest_pers_del as on delete to rtest_person do + delete from rtest_admin where pname = current.pname; + +-- +-- Tables and rules for the logging test +-- +create table rtest_emp (ename char(20), salary money); +create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); +create table rtest_empmass (ename char(20), salary money); + +create rule rtest_emp_ins as on insert to rtest_emp do + insert into rtest_emplog values (new.ename, current_user, + 'hired', new.salary, '0.00'); + +create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do + insert into rtest_emplog values (new.ename, current_user, + 'honored', new.salary, current.salary); + +create rule rtest_emp_del as on delete to rtest_emp do + insert into rtest_emplog values (current.ename, current_user, + 'fired', '0.00', current.salary); + +-- +-- Tables and rules for the multiple cascaded qualified instead +-- rule test +-- +create table rtest_t4 (a int4, b text); +create table rtest_t5 (a int4, b text); +create table rtest_t6 (a int4, b text); +create table rtest_t7 (a int4, b text); +create table rtest_t8 (a int4, b text); +create table rtest_t9 (a int4, b text); + +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 values (new.a, new.b); + +create rule rtest_t4_ins2 as on insert to rtest_t4 + where new.a >= 20 and new.a < 30 do + insert into rtest_t6 values (new.a, new.b); + +create rule rtest_t5_ins as on insert to rtest_t5 + where new.a > 15 do + insert into rtest_t7 values (new.a, new.b); + +create rule rtest_t6_ins as on insert to rtest_t6 + where new.a > 25 do instead + insert into rtest_t8 values (new.a, new.b); + +-- +-- Tables and rules for the rule fire order test +-- +create table rtest_order1 (a int4); +create table rtest_order2 (a int4, b int4, c text); + +create sequence rtest_seq; + +create rule rtest_order_r3 as on insert to rtest_order1 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 3 - this should run 3rd or 4th'); + +create rule rtest_order_r4 as on insert to rtest_order1 + where a < 100 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 4 - this should run 2nd'); + +create rule rtest_order_r2 as on insert to rtest_order1 do + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 2 - this should run 1st'); + +create rule rtest_order_r1 as on insert to rtest_order1 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 1 - this should run 3rd or 4th'); + +-- +-- Tables and rules for the instead nothing test +-- +create table rtest_nothn1 (a int4, b text); +create table rtest_nothn2 (a int4, b text); +create table rtest_nothn3 (a int4, b text); +create table rtest_nothn4 (a int4, b text); + +create rule rtest_nothn_r1 as on insert to rtest_nothn1 + where new.a >= 10 and new.a < 20 do instead (select 1); + +create rule rtest_nothn_r2 as on insert to rtest_nothn1 + where new.a >= 30 and new.a < 40 do instead nothing; + +create rule rtest_nothn_r3 as on insert to rtest_nothn2 + where new.a >= 100 do instead + insert into rtest_nothn3 values (new.a, new.b); + +create rule rtest_nothn_r4 as on insert to rtest_nothn2 + do instead nothing; + +-- +-- Tests on a view that is select * of a table +-- and has insert/update/delete instead rules to +-- behave close like the real table. +-- + +-- +-- We need test date later +-- +insert into rtest_t2 values (1, 21); +insert into rtest_t2 values (2, 22); +insert into rtest_t2 values (3, 23); + +insert into rtest_t3 values (1, 31); +insert into rtest_t3 values (2, 32); +insert into rtest_t3 values (3, 33); +insert into rtest_t3 values (4, 34); +insert into rtest_t3 values (5, 35); + +-- insert values +insert into rtest_v1 values (1, 11); +insert into rtest_v1 values (2, 12); +select * from rtest_v1; + +-- delete with constant expression +delete from rtest_v1 where a = 1; +select * from rtest_v1; +insert into rtest_v1 values (1, 11); +delete from rtest_v1 where b = 12; +select * from rtest_v1; +insert into rtest_v1 values (2, 12); +insert into rtest_v1 values (2, 13); +select * from rtest_v1; +** Remember the delete rule on rtest_v1: It says +** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a +** So this time both rows with a = 2 must get deleted +\p +\r +delete from rtest_v1 where b = 12; +select * from rtest_v1; +delete from rtest_v1; + +-- insert select +insert into rtest_v1 select * from rtest_t2; +select * from rtest_v1; +delete from rtest_v1; + +-- same with swapped targetlist +insert into rtest_v1 (b, a) select b, a from rtest_t2; +select * from rtest_v1; + +-- now with only one target attribute +insert into rtest_v1 (a) select a from rtest_t3; +select * from rtest_v1; +select * from rtest_v1 where b isnull; + +-- let attribute a differ (must be done on rtest_t1 - see above) +update rtest_t1 set a = a + 10 where b isnull; +delete from rtest_v1 where b isnull; +select * from rtest_v1; + +-- now updates with constant expression +update rtest_v1 set b = 42 where a = 2; +select * from rtest_v1; +update rtest_v1 set b = 99 where b = 42; +select * from rtest_v1; +update rtest_v1 set b = 88 where b < 50; +select * from rtest_v1; +delete from rtest_v1; +insert into rtest_v1 select rtest_t2.a, rtest_t3.b where rtest_t2.a = rtest_t3.a; +select * from rtest_v1; + +-- updates in a mergejoin +update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a; +select * from rtest_v1; +insert into rtest_v1 select * from rtest_t3; +select * from rtest_v1; +update rtest_t1 set a = a + 10 where b > 30; +select * from rtest_v1; +update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b; +select * from rtest_v1; + +-- +-- Test for constraint updates/deletes +-- +insert into rtest_system values ('orion', 'Linux Jan Wieck'); +insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); +insert into rtest_system values ('neptun', 'Fileserver'); + +insert into rtest_interface values ('orion', 'eth0'); +insert into rtest_interface values ('orion', 'eth1'); +insert into rtest_interface values ('notjw', 'eth0'); +insert into rtest_interface values ('neptun', 'eth0'); + +insert into rtest_person values ('jw', 'Jan Wieck'); +insert into rtest_person values ('bm', 'Bruce Momjian'); + +insert into rtest_admin values ('jw', 'orion'); +insert into rtest_admin values ('jw', 'notjw'); +insert into rtest_admin values ('bm', 'neptun'); + +update rtest_system set sysname = 'pluto' where sysname = 'neptun'; + +select * from rtest_interface; +select * from rtest_admin; + +update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck'; + +select * from rtest_admin; + +delete from rtest_system where sysname = 'orion'; + +select * from rtest_interface; +select * from rtest_admin; + +-- +-- Rule qualification test +-- +insert into rtest_emp values ('wiech', '5000.00'); +insert into rtest_emp values ('gates', '80000.00'); +update rtest_emp set ename = 'wiecx' where ename = 'wiech'; +update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; +update rtest_emp set salary = '7000.00' where ename = 'wieck'; +delete from rtest_emp where ename = 'gates'; + +select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +insert into rtest_empmass values ('meyer', '4000.00'); +insert into rtest_empmass values ('maier', '5000.00'); +insert into rtest_empmass values ('mayr', '6000.00'); +insert into rtest_emp select * from rtest_empmass; +select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +update rtest_empmass set salary = salary + '1000.00'; +update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename; +select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; +delete from rtest_emp where ename = rtest_empmass.ename; +select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog; + +-- +-- Multiple cascaded qualified instead rule test +-- +insert into rtest_t4 values (1, 'Record should go to rtest_t4'); +insert into rtest_t4 values (2, 'Record should go to rtest_t4'); +insert into rtest_t4 values (10, 'Record should go to rtest_t5'); +insert into rtest_t4 values (15, 'Record should go to rtest_t5'); +insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); +insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); +insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); +insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); +insert into rtest_t4 values (30, 'Record should go to rtest_t4'); +insert into rtest_t4 values (40, 'Record should go to rtest_t4'); + +select * from rtest_t4; +select * from rtest_t5; +select * from rtest_t6; +select * from rtest_t7; +select * from rtest_t8; + +delete from rtest_t4; +delete from rtest_t5; +delete from rtest_t6; +delete from rtest_t7; +delete from rtest_t8; + +insert into rtest_t9 values (1, 'Record should go to rtest_t4'); +insert into rtest_t9 values (2, 'Record should go to rtest_t4'); +insert into rtest_t9 values (10, 'Record should go to rtest_t5'); +insert into rtest_t9 values (15, 'Record should go to rtest_t5'); +insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); +insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); +insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); +insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); +insert into rtest_t9 values (30, 'Record should go to rtest_t4'); +insert into rtest_t9 values (40, 'Record should go to rtest_t4'); + +insert into rtest_t4 select * from rtest_t9 where a < 20; + +select * from rtest_t4; +select * from rtest_t5; +select * from rtest_t6; +select * from rtest_t7; +select * from rtest_t8; + +insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; + +select * from rtest_t4; +select * from rtest_t5; +select * from rtest_t6; +select * from rtest_t7; +select * from rtest_t8; + +insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); + +select * from rtest_t4; +select * from rtest_t5; +select * from rtest_t6; +select * from rtest_t7; +select * from rtest_t8; + +-- +-- Check that the ordering of rules fired is correct +-- +insert into rtest_order1 values (1); +select * from rtest_order2; + +-- +-- Check if instead nothing w/without qualification works +-- +insert into rtest_nothn1 values (1, 'want this'); +insert into rtest_nothn1 values (2, 'want this'); +insert into rtest_nothn1 values (10, 'don''t want this'); +insert into rtest_nothn1 values (19, 'don''t want this'); +insert into rtest_nothn1 values (20, 'want this'); +insert into rtest_nothn1 values (29, 'want this'); +insert into rtest_nothn1 values (30, 'don''t want this'); +insert into rtest_nothn1 values (39, 'don''t want this'); +insert into rtest_nothn1 values (40, 'want this'); +insert into rtest_nothn1 values (50, 'want this'); +insert into rtest_nothn1 values (60, 'want this'); + +select * from rtest_nothn1; + +insert into rtest_nothn2 values (10, 'too small'); +insert into rtest_nothn2 values (50, 'too small'); +insert into rtest_nothn2 values (100, 'OK'); +insert into rtest_nothn2 values (200, 'OK'); + +select * from rtest_nothn2; +select * from rtest_nothn3; + +delete from rtest_nothn1; +delete from rtest_nothn2; +delete from rtest_nothn3; + +insert into rtest_nothn4 values (1, 'want this'); +insert into rtest_nothn4 values (2, 'want this'); +insert into rtest_nothn4 values (10, 'don''t want this'); +insert into rtest_nothn4 values (19, 'don''t want this'); +insert into rtest_nothn4 values (20, 'want this'); +insert into rtest_nothn4 values (29, 'want this'); +insert into rtest_nothn4 values (30, 'don''t want this'); +insert into rtest_nothn4 values (39, 'don''t want this'); +insert into rtest_nothn4 values (40, 'want this'); +insert into rtest_nothn4 values (50, 'want this'); +insert into rtest_nothn4 values (60, 'want this'); + +insert into rtest_nothn1 select * from rtest_nothn4; + +select * from rtest_nothn1; + +delete from rtest_nothn4; + +insert into rtest_nothn4 values (10, 'too small'); +insert into rtest_nothn4 values (50, 'too small'); +insert into rtest_nothn4 values (100, 'OK'); +insert into rtest_nothn4 values (200, 'OK'); + +insert into rtest_nothn2 select * from rtest_nothn4; + +select * from rtest_nothn2; +select * from rtest_nothn3; + diff --git a/src/test/regress/sql/run_ruletest.sql b/src/test/regress/sql/run_ruletest.sql deleted file mode 100644 index ea18e68aa57..00000000000 --- a/src/test/regress/sql/run_ruletest.sql +++ /dev/null @@ -1,261 +0,0 @@ --- --- Tests on a view that is select * of a table --- and has insert/update/delete instead rules to --- behave close like the real table. --- - --- --- We need test date later --- -insert into rtest_t2 values (1, 21); -insert into rtest_t2 values (2, 22); -insert into rtest_t2 values (3, 23); - -insert into rtest_t3 values (1, 31); -insert into rtest_t3 values (2, 32); -insert into rtest_t3 values (3, 33); -insert into rtest_t3 values (4, 34); -insert into rtest_t3 values (5, 35); - --- insert values -insert into rtest_v1 values (1, 11); -insert into rtest_v1 values (2, 12); -select * from rtest_v1; - --- delete with constant expression -delete from rtest_v1 where a = 1; -select * from rtest_v1; -insert into rtest_v1 values (1, 11); -delete from rtest_v1 where b = 12; -select * from rtest_v1; -insert into rtest_v1 values (2, 12); -insert into rtest_v1 values (2, 13); -select * from rtest_v1; -** Remember the delete rule on rtest_v1: It says -** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a -** So this time both rows with a = 2 must get deleted -\p -\r -delete from rtest_v1 where b = 12; -select * from rtest_v1; -delete from rtest_v1; - --- insert select -insert into rtest_v1 select * from rtest_t2; -select * from rtest_v1; -delete from rtest_v1; - --- same with swapped targetlist -insert into rtest_v1 (b, a) select b, a from rtest_t2; -select * from rtest_v1; - --- now with only one target attribute -insert into rtest_v1 (a) select a from rtest_t3; -select * from rtest_v1; -select * from rtest_v1 where b isnull; - --- let attribute a differ (must be done on rtest_t1 - see above) -update rtest_t1 set a = a + 10 where b isnull; -delete from rtest_v1 where b isnull; -select * from rtest_v1; - --- now updates with constant expression -update rtest_v1 set b = 42 where a = 2; -select * from rtest_v1; -update rtest_v1 set b = 99 where b = 42; -select * from rtest_v1; -update rtest_v1 set b = 88 where b < 50; -select * from rtest_v1; -delete from rtest_v1; -insert into rtest_v1 select rtest_t2.a, rtest_t3.b where rtest_t2.a = rtest_t3.a; -select * from rtest_v1; - --- updates in a mergejoin -update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a; -select * from rtest_v1; -insert into rtest_v1 select * from rtest_t3; -select * from rtest_v1; -update rtest_t1 set a = a + 10 where b > 30; -select * from rtest_v1; -update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b; -select * from rtest_v1; - --- --- Test for constraint updates/deletes --- -insert into rtest_system values ('orion', 'Linux Jan Wieck'); -insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); -insert into rtest_system values ('neptun', 'Fileserver'); - -insert into rtest_interface values ('orion', 'eth0'); -insert into rtest_interface values ('orion', 'eth1'); -insert into rtest_interface values ('notjw', 'eth0'); -insert into rtest_interface values ('neptun', 'eth0'); - -insert into rtest_person values ('jw', 'Jan Wieck'); -insert into rtest_person values ('bm', 'Bruce Momjian'); - -insert into rtest_admin values ('jw', 'orion'); -insert into rtest_admin values ('jw', 'notjw'); -insert into rtest_admin values ('bm', 'neptun'); - -update rtest_system set sysname = 'pluto' where sysname = 'neptun'; - -select * from rtest_interface; -select * from rtest_admin; - -update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck'; - -select * from rtest_admin; - -delete from rtest_system where sysname = 'orion'; - -select * from rtest_interface; -select * from rtest_admin; - --- --- Rule qualification test --- -insert into rtest_emp values ('wiech', '5000.00'); -insert into rtest_emp values ('gates', '80000.00'); -update rtest_emp set ename = 'wiecx' where ename = 'wiech'; -update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; -update rtest_emp set salary = '7000.00' where ename = 'wieck'; -delete from rtest_emp where ename = 'gates'; - -select * from rtest_emplog; -insert into rtest_empmass values ('meyer', '4000.00'); -insert into rtest_empmass values ('maier', '5000.00'); -insert into rtest_empmass values ('mayr', '6000.00'); -insert into rtest_emp select * from rtest_empmass; -select * from rtest_emplog; -update rtest_empmass set salary = salary + '1000.00'; -update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename; -select * from rtest_emplog; -delete from rtest_emp where ename = rtest_empmass.ename; -select * from rtest_emplog; - --- --- Multiple cascaded qualified instead rule test --- -insert into rtest_t4 values (1, 'Record should go to rtest_t4'); -insert into rtest_t4 values (2, 'Record should go to rtest_t4'); -insert into rtest_t4 values (10, 'Record should go to rtest_t5'); -insert into rtest_t4 values (15, 'Record should go to rtest_t5'); -insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); -insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); -insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); -insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); -insert into rtest_t4 values (30, 'Record should go to rtest_t4'); -insert into rtest_t4 values (40, 'Record should go to rtest_t4'); - -select * from rtest_t4; -select * from rtest_t5; -select * from rtest_t6; -select * from rtest_t7; -select * from rtest_t8; - -delete from rtest_t4; -delete from rtest_t5; -delete from rtest_t6; -delete from rtest_t7; -delete from rtest_t8; - -insert into rtest_t9 values (1, 'Record should go to rtest_t4'); -insert into rtest_t9 values (2, 'Record should go to rtest_t4'); -insert into rtest_t9 values (10, 'Record should go to rtest_t5'); -insert into rtest_t9 values (15, 'Record should go to rtest_t5'); -insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); -insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); -insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); -insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); -insert into rtest_t9 values (30, 'Record should go to rtest_t4'); -insert into rtest_t9 values (40, 'Record should go to rtest_t4'); - -insert into rtest_t4 select * from rtest_t9 where a < 20; - -select * from rtest_t4; -select * from rtest_t5; -select * from rtest_t6; -select * from rtest_t7; -select * from rtest_t8; - -insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; - -select * from rtest_t4; -select * from rtest_t5; -select * from rtest_t6; -select * from rtest_t7; -select * from rtest_t8; - -insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); - -select * from rtest_t4; -select * from rtest_t5; -select * from rtest_t6; -select * from rtest_t7; -select * from rtest_t8; - --- --- Check that the ordering of rules fired is correct --- -insert into rtest_order1 values (1); -select * from rtest_order2; - --- --- Check if instead nothing w/without qualification works --- -insert into rtest_nothn1 values (1, 'want this'); -insert into rtest_nothn1 values (2, 'want this'); -insert into rtest_nothn1 values (10, 'don''t want this'); -insert into rtest_nothn1 values (19, 'don''t want this'); -insert into rtest_nothn1 values (20, 'want this'); -insert into rtest_nothn1 values (29, 'want this'); -insert into rtest_nothn1 values (30, 'don''t want this'); -insert into rtest_nothn1 values (39, 'don''t want this'); -insert into rtest_nothn1 values (40, 'want this'); -insert into rtest_nothn1 values (50, 'want this'); -insert into rtest_nothn1 values (60, 'want this'); - -select * from rtest_nothn1; - -insert into rtest_nothn2 values (10, 'too small'); -insert into rtest_nothn2 values (50, 'too small'); -insert into rtest_nothn2 values (100, 'OK'); -insert into rtest_nothn2 values (200, 'OK'); - -select * from rtest_nothn2; -select * from rtest_nothn3; - -delete from rtest_nothn1; -delete from rtest_nothn2; -delete from rtest_nothn3; - -insert into rtest_nothn4 values (1, 'want this'); -insert into rtest_nothn4 values (2, 'want this'); -insert into rtest_nothn4 values (10, 'don''t want this'); -insert into rtest_nothn4 values (19, 'don''t want this'); -insert into rtest_nothn4 values (20, 'want this'); -insert into rtest_nothn4 values (29, 'want this'); -insert into rtest_nothn4 values (30, 'don''t want this'); -insert into rtest_nothn4 values (39, 'don''t want this'); -insert into rtest_nothn4 values (40, 'want this'); -insert into rtest_nothn4 values (50, 'want this'); -insert into rtest_nothn4 values (60, 'want this'); - -insert into rtest_nothn1 select * from rtest_nothn4; - -select * from rtest_nothn1; - -delete from rtest_nothn4; - -insert into rtest_nothn4 values (10, 'too small'); -insert into rtest_nothn4 values (50, 'too small'); -insert into rtest_nothn4 values (100, 'OK'); -insert into rtest_nothn4 values (200, 'OK'); - -insert into rtest_nothn2 select * from rtest_nothn4; - -select * from rtest_nothn2; -select * from rtest_nothn3; - diff --git a/src/test/regress/sql/setup_ruletest.sql b/src/test/regress/sql/setup_ruletest.sql deleted file mode 100644 index ea9db0b7437..00000000000 --- a/src/test/regress/sql/setup_ruletest.sql +++ /dev/null @@ -1,139 +0,0 @@ --- --- Tables and rules for the view test --- -create table rtest_t1 (a int4, b int4); -create table rtest_t2 (a int4, b int4); -create table rtest_t3 (a int4, b int4); - -create view rtest_v1 as select * from rtest_t1; -create rule rtest_v1_ins as on insert to rtest_v1 do instead - insert into rtest_t1 values (new.a, new.b); -create rule rtest_v1_upd as on update to rtest_v1 do instead - update rtest_t1 set a = new.a, b = new.b - where a = current.a; -create rule rtest_v1_del as on delete to rtest_v1 do instead - delete from rtest_t1 where a = current.a; - --- --- Tables and rules for the constraint update/delete test --- --- Note: --- psql prevents from putting colons into brackets as --- required for multi action rules. So we define single --- rules for each action required for now --- -create table rtest_system (sysname text, sysdesc text); -create table rtest_interface (sysname text, ifname text); -create table rtest_person (pname text, pdesc text); -create table rtest_admin (pname text, sysname text); - -create rule rtest_sys_upd1 as on update to rtest_system do - update rtest_interface set sysname = new.sysname - where sysname = current.sysname; -create rule rtest_sys_upd2 as on update to rtest_system do - update rtest_admin set sysname = new.sysname - where sysname = current.sysname; - -create rule rtest_sys_del1 as on delete to rtest_system do - delete from rtest_interface where sysname = current.sysname; -create rule rtest_sys_del2 as on delete to rtest_system do - delete from rtest_admin where sysname = current.sysname; - -create rule rtest_pers_upd as on update to rtest_person do - update rtest_admin set pname = new.pname where pname = current.pname; - -create rule rtest_pers_del as on delete to rtest_person do - delete from rtest_admin where pname = current.pname; - --- --- Tables and rules for the logging test --- -create table rtest_emp (ename char(20), salary money); -create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); -create table rtest_empmass (ename char(20), salary money); - -create rule rtest_emp_ins as on insert to rtest_emp do - insert into rtest_emplog values (new.ename, getpgusername(), - 'hired', new.salary, '0.00'); - -create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do - insert into rtest_emplog values (new.ename, getpgusername(), - 'honored', new.salary, current.salary); - -create rule rtest_emp_del as on delete to rtest_emp do - insert into rtest_emplog values (current.ename, getpgusername(), - 'fired', '0.00', current.salary); - --- --- Tables and rules for the multiple cascaded qualified instead --- rule test --- -create table rtest_t4 (a int4, b text); -create table rtest_t5 (a int4, b text); -create table rtest_t6 (a int4, b text); -create table rtest_t7 (a int4, b text); -create table rtest_t8 (a int4, b text); -create table rtest_t9 (a int4, b text); - -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 values (new.a, new.b); - -create rule rtest_t4_ins2 as on insert to rtest_t4 - where new.a >= 20 and new.a < 30 do - insert into rtest_t6 values (new.a, new.b); - -create rule rtest_t5_ins as on insert to rtest_t5 - where new.a > 15 do - insert into rtest_t7 values (new.a, new.b); - -create rule rtest_t6_ins as on insert to rtest_t6 - where new.a > 25 do instead - insert into rtest_t8 values (new.a, new.b); - --- --- Tables and rules for the rule fire order test --- -create table rtest_order1 (a int4); -create table rtest_order2 (a int4, b int4, c text); - -create sequence rtest_seq; - -create rule rtest_order_r3 as on insert to rtest_order1 do instead - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 3 - this should run 3rd or 4th'); - -create rule rtest_order_r4 as on insert to rtest_order1 - where a < 100 do instead - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 4 - this should run 2nd'); - -create rule rtest_order_r2 as on insert to rtest_order1 do - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 2 - this should run 1st'); - -create rule rtest_order_r1 as on insert to rtest_order1 do instead - insert into rtest_order2 values (new.a, nextval('rtest_seq'), - 'rule 1 - this should run 3rd or 4th'); - --- --- Tables and rules for the instead nothing test --- -create table rtest_nothn1 (a int4, b text); -create table rtest_nothn2 (a int4, b text); -create table rtest_nothn3 (a int4, b text); -create table rtest_nothn4 (a int4, b text); - -create rule rtest_nothn_r1 as on insert to rtest_nothn1 - where new.a >= 10 and new.a < 20 do instead (select 1); - -create rule rtest_nothn_r2 as on insert to rtest_nothn1 - where new.a >= 30 and new.a < 40 do instead nothing; - -create rule rtest_nothn_r3 as on insert to rtest_nothn2 - where new.a >= 100 do instead - insert into rtest_nothn3 values (new.a, new.b); - -create rule rtest_nothn_r4 as on insert to rtest_nothn2 - do instead nothing; - diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index e8446796ad5..d4ba89aa345 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -58,5 +58,4 @@ hash_index select_views alter_table portals_p2 -setup_ruletest -run_ruletest +rules -- cgit v1.2.3