where a = old.a;
create rule rtest_v1_del as on delete to rtest_v1 do instead
delete from rtest_t1 where a = old.a;
-
+-- Test comments
+COMMENT ON RULE rtest_v1_bad ON rtest_v1 IS 'bad rule';
+COMMENT ON RULE rtest_v1_del ON rtest_v1 IS 'delete rule';
+COMMENT ON RULE rtest_v1_del ON rtest_v1 IS NULL;
--
-- Tables and rules for the constraint update/delete test
--
create table rtest_person (pname text, pdesc text);
create table rtest_admin (pname text, sysname text);
-create rule rtest_sys_upd as on update to rtest_system do (
+create rule rtest_sys_upd as on update to rtest_system do also (
update rtest_interface set sysname = new.sysname
where sysname = old.sysname;
update rtest_admin set sysname = new.sysname
where sysname = old.sysname
);
-create rule rtest_sys_del as on delete to rtest_system do (
+create rule rtest_sys_del as on delete to rtest_system do also (
delete from rtest_interface where sysname = old.sysname;
delete from rtest_admin where sysname = old.sysname;
);
-create rule rtest_pers_upd as on update to rtest_person do
+create rule rtest_pers_upd as on update to rtest_person do also
update rtest_admin set pname = new.pname where pname = old.pname;
-create rule rtest_pers_del as on delete to rtest_person do
+create rule rtest_pers_del as on delete to rtest_person do also
delete from rtest_admin where pname = old.pname;
--
--
-- Tables and rules for the rule fire order test
--
+-- As of PG 7.3, the rules should fire in order by name, regardless
+-- of INSTEAD attributes or creation order.
+--
create table rtest_order1 (a int4);
create table rtest_order2 (a int4, b int4, c text);
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');
+ 'rule 3 - this should run 3rd');
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');
+ 'rule 4 - this should run 4th');
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');
+ 'rule 2 - this should run 2nd');
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');
+ 'rule 1 - this should run 1st');
--
-- Tables and rules for the instead nothing test
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;
+insert into rtest_v1 select rtest_t2.a, rtest_t3.b
+ from rtest_t2, rtest_t3
+ 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;
+update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.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;
+update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b;
select * from rtest_v1;
--
insert into rtest_emp select * from rtest_empmass;
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
update rtest_empmass set salary = salary + '1000.00';
-update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename;
+update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
-delete from rtest_emp where ename = rtest_empmass.ename;
+delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
--
group by X.a, X.b;
create function rtest_viewfunc1(int4) returns int4 as
'select count(*)::int4 from rtest_view2 where a = $1'
- language 'sql';
+ language sql;
create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount
from rtest_view1;
select * from rtest_vview1;
select * from rtest_vview2;
select * from rtest_vview3;
-select * from rtest_vview4;
+select * from rtest_vview4 order by a, b;
select * from rtest_vview5;
insert into rtest_view3 select * from rtest_vview1 where a < 7;
delete from rtest_view3;
insert into rtest_view4 select * from rtest_vview4 where 3 > refcount;
-select * from rtest_view4;
+select * from rtest_view4 order by a, b;
delete from rtest_view4;
insert into rtest_view4 select * from rtest_vview5 where a > 2 and refcount = 0;
-- SELECTs in doc
SELECT * FROM shoelace ORDER BY sl_name;
-SELECT * FROM shoe_ready WHERE total_avail >= 2;
+SELECT * FROM shoe_ready WHERE total_avail >= 2 ORDER BY 1;
CREATE TABLE shoelace_log (
sl_name char(10), -- shoelace changed
sl_avail integer, -- new available value
log_who name, -- who did it
- log_when datetime -- when
+ log_when timestamp -- when
);
-- Want "log_who" to be CURRENT_USER,
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;
+SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm;
SELECT * FROM shoelace_candelete;
DELETE FROM shoelace WHERE EXISTS
--
-- Check that ruleutils are working
--
-SELECT viewname, definition FROM pg_views ORDER BY viewname;
+SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname;
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
+--
+-- CREATE OR REPLACE RULE
+--
+
+CREATE TABLE ruletest_tbl (a int, b int);
+CREATE TABLE ruletest_tbl2 (a int, b int);
+
+CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
+ DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
+
+INSERT INTO ruletest_tbl VALUES (99, 99);
+
+CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
+ DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
+
+INSERT INTO ruletest_tbl VALUES (99, 99);
+
+SELECT * FROM ruletest_tbl2;
+
+-- 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)
+);
+
+create table rule_and_refint_t2 (
+ id2a integer,
+ id2c integer,
+
+ primary key (id2a, id2c)
+);
+
+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)
+);
+
+
+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');
+insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
+
+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');
+insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
+
+--
+-- check for planner problems with complex inherited UPDATES
+--
+
+create table id (id serial primary key, name text);
+-- currently, must respecify PKEY for each inherited subtable
+create table test_1 (id integer primary key) inherits (id);
+create table test_2 (id integer primary key) inherits (id);
+create table test_3 (id integer primary key) inherits (id);
+
+insert into test_1 (name) values ('Test 1');
+insert into test_1 (name) values ('Test 2');
+insert into test_2 (name) values ('Test 3');
+insert into test_2 (name) values ('Test 4');
+insert into test_3 (name) values ('Test 5');
+insert into test_3 (name) values ('Test 6');
+
+create view id_ordered as select * from id order by id;
+
+create rule update_id_ordered as on update to id_ordered
+ do instead update id set name = new.name where id = old.id;
+
+select * from id_ordered;
+update id_ordered set name = 'update 2' where id = 2;
+update id_ordered set name = 'update 4' where id = 4;
+update id_ordered set name = 'update 5' where id = 5;
+select * from id_ordered;
+
+set client_min_messages to warning; -- suppress cascade notices
+drop table id cascade;
+reset client_min_messages;
+
+--
+-- check corner case where an entirely-dummy subplan is created by
+-- constraint exclusion
+--
+
+create temp table t1 (a integer primary key);
+
+create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1);
+create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1);
+
+create rule t1_ins_1 as on insert to t1
+ where new.a >= 0 and new.a < 10
+ do instead
+ insert into t1_1 values (new.a);
+create rule t1_ins_2 as on insert to t1
+ where new.a >= 10 and new.a < 20
+ do instead
+ insert into t1_2 values (new.a);
+
+create rule t1_upd_1 as on update to t1
+ where old.a >= 0 and old.a < 10
+ do instead
+ update t1_1 set a = new.a where a = old.a;
+create rule t1_upd_2 as on update to t1
+ where old.a >= 10 and old.a < 20
+ do instead
+ update t1_2 set a = new.a where a = old.a;
+
+set constraint_exclusion = on;
+
+insert into t1 select * from generate_series(5,19,1) g;
+update t1 set a = 4 where a = 5;
+
+select * from only t1;
+select * from only t1_1;
+select * from only t1_2;
projects / postgresql / blobdiff