diff options
Diffstat (limited to 'src/test/regress/expected/rules.out')
| -rw-r--r-- | src/test/regress/expected/rules.out | 62 |
1 files changed, 59 insertions, 3 deletions
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index c0d84fd1bcd..a46a1a6596f 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1317,9 +1317,9 @@ SELECT tablename, rulename, definition FROM pg_rules ---------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; - 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_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 NOTHING; 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); @@ -1364,3 +1364,59 @@ SELECT * FROM ruletest_tbl2; 1000 | 1000 (2 rows) +-- Check that rewrite rules splitting one INSERT into multiple +-- conditional statements does not disable FK checking. +create table rule_and_refint_t1 ( + id1a integer, + id1b integer, + + primary key (id1a, id1b) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'rule_and_refint_t1_pkey' for table 'rule_and_refint_t1' +create table rule_and_refint_t2 ( + id2a integer, + id2c integer, + + primary key (id2a, id2c) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'rule_and_refint_t2_pkey' for table 'rule_and_refint_t2' +create table rule_and_refint_t3 ( + id3a integer, + id3b integer, + id3c integer, + data text, + primary key (id3a, id3b, id3c), + foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), + foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'rule_and_refint_t3_pkey' for table 'rule_and_refint_t3' +NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) +insert into rule_and_refint_t1 values (1, 11); +insert into rule_and_refint_t1 values (1, 12); +insert into rule_and_refint_t1 values (2, 21); +insert into rule_and_refint_t1 values (2, 22); +insert into rule_and_refint_t2 values (1, 11); +insert into rule_and_refint_t2 values (1, 12); +insert into rule_and_refint_t2 values (2, 21); +insert into rule_and_refint_t2 values (2, 22); +insert into rule_and_refint_t3 values (1, 11, 11, 'row1'); +insert into rule_and_refint_t3 values (1, 11, 12, 'row2'); +insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); +insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); +insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); +ERROR: $2 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t2 +insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); +ERROR: $1 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t1 +create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 + where (exists (select 1 from rule_and_refint_t3 + where (((rule_and_refint_t3.id3a = new.id3a) + and (rule_and_refint_t3.id3b = new.id3b)) + and (rule_and_refint_t3.id3c = new.id3c)))) + do instead update rule_and_refint_t3 set data = new.data + where (((rule_and_refint_t3.id3a = new.id3a) + and (rule_and_refint_t3.id3b = new.id3b)) + and (rule_and_refint_t3.id3c = new.id3c)); +insert into rule_and_refint_t3 values (1, 11, 13, 'row7'); +ERROR: $2 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t2 +insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); +ERROR: $1 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t1 |