src/test/regress/sql/rules.sql

   1 --
   2 -- RULES
   3 -- From Jan's original setup_ruletest.sql and run_ruletest.sql
   4 -- - thomas 1998-09-13
   5 --
   6
   7 --
   8 -- Tables and rules for the view test
   9 --
  10 create table rtest_t1 (a int4, b int4);
  11 create table rtest_t2 (a int4, b int4);
  12 create table rtest_t3 (a int4, b int4);
  13
  14 create view rtest_v1 as select * from rtest_t1;
  15 create rule rtest_v1_ins as on insert to rtest_v1 do instead
  16         insert into rtest_t1 values (new.a, new.b);
  17 create rule rtest_v1_upd as on update to rtest_v1 do instead
  18         update rtest_t1 set a = new.a, b = new.b
  19         where a = old.a;
  20 create rule rtest_v1_del as on delete to rtest_v1 do instead
  21         delete from rtest_t1 where a = old.a;
  22 -- Test comments
  23 COMMENT ON RULE rtest_v1_bad ON rtest_v1 IS 'bad rule';
  24 COMMENT ON RULE rtest_v1_del ON rtest_v1 IS 'delete rule';
  25 COMMENT ON RULE rtest_v1_del ON rtest_v1 IS NULL;
  26 --
  27 -- Tables and rules for the constraint update/delete test
  28 --
  29 -- Note:
  30 --      Now that we have multiple action rule support, we check
  31 --      both possible syntaxes to define them (The last action
  32 --  can but must not have a semicolon at the end).
  33 --
  34 create table rtest_system (sysname text, sysdesc text);
  35 create table rtest_interface (sysname text, ifname text);
  36 create table rtest_person (pname text, pdesc text);
  37 create table rtest_admin (pname text, sysname text);
  38
  39 create rule rtest_sys_upd as on update to rtest_system do also (
  40         update rtest_interface set sysname = new.sysname
  41                 where sysname = old.sysname;
  42         update rtest_admin set sysname = new.sysname
  43                 where sysname = old.sysname
  44         );
  45
  46 create rule rtest_sys_del as on delete to rtest_system do also (
  47         delete from rtest_interface where sysname = old.sysname;
  48         delete from rtest_admin where sysname = old.sysname;
  49         );
  50
  51 create rule rtest_pers_upd as on update to rtest_person do also
  52         update rtest_admin set pname = new.pname where pname = old.pname;
  53
  54 create rule rtest_pers_del as on delete to rtest_person do also
  55         delete from rtest_admin where pname = old.pname;
  56
  57 --
  58 -- Tables and rules for the logging test
  59 --
  60 create table rtest_emp (ename char(20), salary money);
  61 create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money);
  62 create table rtest_empmass (ename char(20), salary money);
  63
  64 create rule rtest_emp_ins as on insert to rtest_emp do
  65         insert into rtest_emplog values (new.ename, current_user,
  66                         'hired', new.salary, '0.00');
  67
  68 create rule rtest_emp_upd as on update to rtest_emp where new.salary != old.salary do
  69         insert into rtest_emplog values (new.ename, current_user,
  70                         'honored', new.salary, old.salary);
  71
  72 create rule rtest_emp_del as on delete to rtest_emp do
  73         insert into rtest_emplog values (old.ename, current_user,
  74                         'fired', '0.00', old.salary);
  75
  76 --
  77 -- Tables and rules for the multiple cascaded qualified instead
  78 -- rule test
  79 --
  80 create table rtest_t4 (a int4, b text);
  81 create table rtest_t5 (a int4, b text);
  82 create table rtest_t6 (a int4, b text);
  83 create table rtest_t7 (a int4, b text);
  84 create table rtest_t8 (a int4, b text);
  85 create table rtest_t9 (a int4, b text);
  86
  87 create rule rtest_t4_ins1 as on insert to rtest_t4
  88                 where new.a >= 10 and new.a < 20 do instead
  89         insert into rtest_t5 values (new.a, new.b);
  90
  91 create rule rtest_t4_ins2 as on insert to rtest_t4
  92                 where new.a >= 20 and new.a < 30 do
  93         insert into rtest_t6 values (new.a, new.b);
  94
  95 create rule rtest_t5_ins as on insert to rtest_t5
  96                 where new.a > 15 do
  97         insert into rtest_t7 values (new.a, new.b);
  98
  99 create rule rtest_t6_ins as on insert to rtest_t6
 100                 where new.a > 25 do instead
 101         insert into rtest_t8 values (new.a, new.b);
 102
 103 --
 104 -- Tables and rules for the rule fire order test
 105 --
 106 -- As of PG 7.3, the rules should fire in order by name, regardless
 107 -- of INSTEAD attributes or creation order.
 108 --
 109 create table rtest_order1 (a int4);
 110 create table rtest_order2 (a int4, b int4, c text);
 111
 112 create sequence rtest_seq;
 113
 114 create rule rtest_order_r3 as on insert to rtest_order1 do instead
 115         insert into rtest_order2 values (new.a, nextval('rtest_seq'),
 116                 'rule 3 - this should run 3rd');
 117
 118 create rule rtest_order_r4 as on insert to rtest_order1
 119                 where a < 100 do instead
 120         insert into rtest_order2 values (new.a, nextval('rtest_seq'),
 121                 'rule 4 - this should run 4th');
 122
 123 create rule rtest_order_r2 as on insert to rtest_order1 do
 124         insert into rtest_order2 values (new.a, nextval('rtest_seq'),
 125                 'rule 2 - this should run 2nd');
 126
 127 create rule rtest_order_r1 as on insert to rtest_order1 do instead
 128         insert into rtest_order2 values (new.a, nextval('rtest_seq'),
 129                 'rule 1 - this should run 1st');
 130
 131 --
 132 -- Tables and rules for the instead nothing test
 133 --
 134 create table rtest_nothn1 (a int4, b text);
 135 create table rtest_nothn2 (a int4, b text);
 136 create table rtest_nothn3 (a int4, b text);
 137 create table rtest_nothn4 (a int4, b text);
 138
 139 create rule rtest_nothn_r1 as on insert to rtest_nothn1
 140         where new.a >= 10 and new.a < 20 do instead nothing;
 141
 142 create rule rtest_nothn_r2 as on insert to rtest_nothn1
 143         where new.a >= 30 and new.a < 40 do instead nothing;
 144
 145 create rule rtest_nothn_r3 as on insert to rtest_nothn2
 146         where new.a >= 100 do instead
 147         insert into rtest_nothn3 values (new.a, new.b);
 148
 149 create rule rtest_nothn_r4 as on insert to rtest_nothn2
 150         do instead nothing;
 151
 152 --
 153 -- Tests on a view that is select * of a table
 154 -- and has insert/update/delete instead rules to
 155 -- behave close like the real table.
 156 --
 157
 158 --
 159 -- We need test date later
 160 --
 161 insert into rtest_t2 values (1, 21);
 162 insert into rtest_t2 values (2, 22);
 163 insert into rtest_t2 values (3, 23);
 164
 165 insert into rtest_t3 values (1, 31);
 166 insert into rtest_t3 values (2, 32);
 167 insert into rtest_t3 values (3, 33);
 168 insert into rtest_t3 values (4, 34);
 169 insert into rtest_t3 values (5, 35);
 170
 171 -- insert values
 172 insert into rtest_v1 values (1, 11);
 173 insert into rtest_v1 values (2, 12);
 174 select * from rtest_v1;
 175
 176 -- delete with constant expression
 177 delete from rtest_v1 where a = 1;
 178 select * from rtest_v1;
 179 insert into rtest_v1 values (1, 11);
 180 delete from rtest_v1 where b = 12;
 181 select * from rtest_v1;
 182 insert into rtest_v1 values (2, 12);
 183 insert into rtest_v1 values (2, 13);
 184 select * from rtest_v1;
 185 ** Remember the delete rule on rtest_v1: It says
 186 ** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a
 187 ** So this time both rows with a = 2 must get deleted
 188 \p
 189 \r
 190 delete from rtest_v1 where b = 12;
 191 select * from rtest_v1;
 192 delete from rtest_v1;
 193
 194 -- insert select
 195 insert into rtest_v1 select * from rtest_t2;
 196 select * from rtest_v1;
 197 delete from rtest_v1;
 198
 199 -- same with swapped targetlist
 200 insert into rtest_v1 (b, a) select b, a from rtest_t2;
 201 select * from rtest_v1;
 202
 203 -- now with only one target attribute
 204 insert into rtest_v1 (a) select a from rtest_t3;
 205 select * from rtest_v1;
 206 select * from rtest_v1 where b isnull;
 207
 208 -- let attribute a differ (must be done on rtest_t1 - see above)
 209 update rtest_t1 set a = a + 10 where b isnull;
 210 delete from rtest_v1 where b isnull;
 211 select * from rtest_v1;
 212
 213 -- now updates with constant expression
 214 update rtest_v1 set b = 42 where a = 2;
 215 select * from rtest_v1;
 216 update rtest_v1 set b = 99 where b = 42;
 217 select * from rtest_v1;
 218 update rtest_v1 set b = 88 where b < 50;
 219 select * from rtest_v1;
 220 delete from rtest_v1;
 221 insert into rtest_v1 select rtest_t2.a, rtest_t3.b
 222     from rtest_t2, rtest_t3
 223     where rtest_t2.a = rtest_t3.a;
 224 select * from rtest_v1;
 225
 226 -- updates in a mergejoin
 227 update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.a = rtest_t2.a;
 228 select * from rtest_v1;
 229 insert into rtest_v1 select * from rtest_t3;
 230 select * from rtest_v1;
 231 update rtest_t1 set a = a + 10 where b > 30;
 232 select * from rtest_v1;
 233 update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b;
 234 select * from rtest_v1;
 235
 236 --
 237 -- Test for constraint updates/deletes
 238 --
 239 insert into rtest_system values ('orion', 'Linux Jan Wieck');
 240 insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)');
 241 insert into rtest_system values ('neptun', 'Fileserver');
 242
 243 insert into rtest_interface values ('orion', 'eth0');
 244 insert into rtest_interface values ('orion', 'eth1');
 245 insert into rtest_interface values ('notjw', 'eth0');
 246 insert into rtest_interface values ('neptun', 'eth0');
 247
 248 insert into rtest_person values ('jw', 'Jan Wieck');
 249 insert into rtest_person values ('bm', 'Bruce Momjian');
 250
 251 insert into rtest_admin values ('jw', 'orion');
 252 insert into rtest_admin values ('jw', 'notjw');
 253 insert into rtest_admin values ('bm', 'neptun');
 254
 255 update rtest_system set sysname = 'pluto' where sysname = 'neptun';
 256
 257 select * from rtest_interface;
 258 select * from rtest_admin;
 259
 260 update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck';
 261
 262 -- Note: use ORDER BY here to ensure consistent output across all systems.
 263 -- The above UPDATE affects two rows with equal keys, so they could be
 264 -- updated in either order depending on the whim of the local qsort().
 265
 266 select * from rtest_admin order by pname, sysname;
 267
 268 delete from rtest_system where sysname = 'orion';
 269
 270 select * from rtest_interface;
 271 select * from rtest_admin;
 272
 273 --
 274 -- Rule qualification test
 275 --
 276 insert into rtest_emp values ('wiech', '5000.00');
 277 insert into rtest_emp values ('gates', '80000.00');
 278 update rtest_emp set ename = 'wiecx' where ename = 'wiech';
 279 update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx';
 280 update rtest_emp set salary = '7000.00' where ename = 'wieck';
 281 delete from rtest_emp where ename = 'gates';
 282
 283 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 284 insert into rtest_empmass values ('meyer', '4000.00');
 285 insert into rtest_empmass values ('maier', '5000.00');
 286 insert into rtest_empmass values ('mayr', '6000.00');
 287 insert into rtest_emp select * from rtest_empmass;
 288 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 289 update rtest_empmass set salary = salary + '1000.00';
 290 update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
 291 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 292 delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
 293 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 294
 295 --
 296 -- Multiple cascaded qualified instead rule test
 297 --
 298 insert into rtest_t4 values (1, 'Record should go to rtest_t4');
 299 insert into rtest_t4 values (2, 'Record should go to rtest_t4');
 300 insert into rtest_t4 values (10, 'Record should go to rtest_t5');
 301 insert into rtest_t4 values (15, 'Record should go to rtest_t5');
 302 insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7');
 303 insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6');
 304 insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8');
 305 insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8');
 306 insert into rtest_t4 values (30, 'Record should go to rtest_t4');
 307 insert into rtest_t4 values (40, 'Record should go to rtest_t4');
 308
 309 select * from rtest_t4;
 310 select * from rtest_t5;
 311 select * from rtest_t6;
 312 select * from rtest_t7;
 313 select * from rtest_t8;
 314
 315 delete from rtest_t4;
 316 delete from rtest_t5;
 317 delete from rtest_t6;
 318 delete from rtest_t7;
 319 delete from rtest_t8;
 320
 321 insert into rtest_t9 values (1, 'Record should go to rtest_t4');
 322 insert into rtest_t9 values (2, 'Record should go to rtest_t4');
 323 insert into rtest_t9 values (10, 'Record should go to rtest_t5');
 324 insert into rtest_t9 values (15, 'Record should go to rtest_t5');
 325 insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7');
 326 insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6');
 327 insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8');
 328 insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8');
 329 insert into rtest_t9 values (30, 'Record should go to rtest_t4');
 330 insert into rtest_t9 values (40, 'Record should go to rtest_t4');
 331
 332 insert into rtest_t4 select * from rtest_t9 where a < 20;
 333
 334 select * from rtest_t4;
 335 select * from rtest_t5;
 336 select * from rtest_t6;
 337 select * from rtest_t7;
 338 select * from rtest_t8;
 339
 340 insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8';
 341
 342 select * from rtest_t4;
 343 select * from rtest_t5;
 344 select * from rtest_t6;
 345 select * from rtest_t7;
 346 select * from rtest_t8;
 347
 348 insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40);
 349
 350 select * from rtest_t4;
 351 select * from rtest_t5;
 352 select * from rtest_t6;
 353 select * from rtest_t7;
 354 select * from rtest_t8;
 355
 356 --
 357 -- Check that the ordering of rules fired is correct
 358 --
 359 insert into rtest_order1 values (1);
 360 select * from rtest_order2;
 361
 362 --
 363 -- Check if instead nothing w/without qualification works
 364 --
 365 insert into rtest_nothn1 values (1, 'want this');
 366 insert into rtest_nothn1 values (2, 'want this');
 367 insert into rtest_nothn1 values (10, 'don''t want this');
 368 insert into rtest_nothn1 values (19, 'don''t want this');
 369 insert into rtest_nothn1 values (20, 'want this');
 370 insert into rtest_nothn1 values (29, 'want this');
 371 insert into rtest_nothn1 values (30, 'don''t want this');
 372 insert into rtest_nothn1 values (39, 'don''t want this');
 373 insert into rtest_nothn1 values (40, 'want this');
 374 insert into rtest_nothn1 values (50, 'want this');
 375 insert into rtest_nothn1 values (60, 'want this');
 376
 377 select * from rtest_nothn1;
 378
 379 insert into rtest_nothn2 values (10, 'too small');
 380 insert into rtest_nothn2 values (50, 'too small');
 381 insert into rtest_nothn2 values (100, 'OK');
 382 insert into rtest_nothn2 values (200, 'OK');
 383
 384 select * from rtest_nothn2;
 385 select * from rtest_nothn3;
 386
 387 delete from rtest_nothn1;
 388 delete from rtest_nothn2;
 389 delete from rtest_nothn3;
 390
 391 insert into rtest_nothn4 values (1, 'want this');
 392 insert into rtest_nothn4 values (2, 'want this');
 393 insert into rtest_nothn4 values (10, 'don''t want this');
 394 insert into rtest_nothn4 values (19, 'don''t want this');
 395 insert into rtest_nothn4 values (20, 'want this');
 396 insert into rtest_nothn4 values (29, 'want this');
 397 insert into rtest_nothn4 values (30, 'don''t want this');
 398 insert into rtest_nothn4 values (39, 'don''t want this');
 399 insert into rtest_nothn4 values (40, 'want this');
 400 insert into rtest_nothn4 values (50, 'want this');
 401 insert into rtest_nothn4 values (60, 'want this');
 402
 403 insert into rtest_nothn1 select * from rtest_nothn4;
 404
 405 select * from rtest_nothn1;
 406
 407 delete from rtest_nothn4;
 408
 409 insert into rtest_nothn4 values (10, 'too small');
 410 insert into rtest_nothn4 values (50, 'too small');
 411 insert into rtest_nothn4 values (100, 'OK');
 412 insert into rtest_nothn4 values (200, 'OK');
 413
 414 insert into rtest_nothn2 select * from rtest_nothn4;
 415
 416 select * from rtest_nothn2;
 417 select * from rtest_nothn3;
 418
 419 create table rtest_view1 (a int4, b text, v bool);
 420 create table rtest_view2 (a int4);
 421 create table rtest_view3 (a int4, b text);
 422 create table rtest_view4 (a int4, b text, c int4);
 423 create view rtest_vview1 as select a, b from rtest_view1 X
 424         where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
 425 create view rtest_vview2 as select a, b from rtest_view1 where v;
 426 create view rtest_vview3 as select a, b from rtest_vview2 X
 427         where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
 428 create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount
 429         from rtest_view1 X, rtest_view2 Y
 430         where X.a = Y.a
 431         group by X.a, X.b;
 432 create function rtest_viewfunc1(int4) returns int4 as
 433         'select count(*)::int4 from rtest_view2 where a = $1'
 434         language sql;
 435 create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount
 436         from rtest_view1;
 437
 438 insert into rtest_view1 values (1, 'item 1', 't');
 439 insert into rtest_view1 values (2, 'item 2', 't');
 440 insert into rtest_view1 values (3, 'item 3', 't');
 441 insert into rtest_view1 values (4, 'item 4', 'f');
 442 insert into rtest_view1 values (5, 'item 5', 't');
 443 insert into rtest_view1 values (6, 'item 6', 'f');
 444 insert into rtest_view1 values (7, 'item 7', 't');
 445 insert into rtest_view1 values (8, 'item 8', 't');
 446
 447 insert into rtest_view2 values (2);
 448 insert into rtest_view2 values (2);
 449 insert into rtest_view2 values (4);
 450 insert into rtest_view2 values (5);
 451 insert into rtest_view2 values (7);
 452 insert into rtest_view2 values (7);
 453 insert into rtest_view2 values (7);
 454 insert into rtest_view2 values (7);
 455
 456 select * from rtest_vview1;
 457 select * from rtest_vview2;
 458 select * from rtest_vview3;
 459 select * from rtest_vview4 order by a, b;
 460 select * from rtest_vview5;
 461
 462 insert into rtest_view3 select * from rtest_vview1 where a < 7;
 463 select * from rtest_view3;
 464 delete from rtest_view3;
 465
 466 insert into rtest_view3 select * from rtest_vview2 where a != 5 and b !~ '2';
 467 select * from rtest_view3;
 468 delete from rtest_view3;
 469
 470 insert into rtest_view3 select * from rtest_vview3;
 471 select * from rtest_view3;
 472 delete from rtest_view3;
 473
 474 insert into rtest_view4 select * from rtest_vview4 where 3 > refcount;
 475 select * from rtest_view4 order by a, b;
 476 delete from rtest_view4;
 477
 478 insert into rtest_view4 select * from rtest_vview5 where a > 2 and refcount = 0;
 479 select * from rtest_view4;
 480 delete from rtest_view4;
 481 --
 482 -- Test for computations in views
 483 --
 484 create table rtest_comp (
 485         part    text,
 486         unit    char(4),
 487         size    float
 488 );
 489
 490
 491 create table rtest_unitfact (
 492         unit    char(4),
 493         factor  float
 494 );
 495
 496 create view rtest_vcomp as
 497         select X.part, (X.size * Y.factor) as size_in_cm
 498                         from rtest_comp X, rtest_unitfact Y
 499                         where X.unit = Y.unit;
 500
 501
 502 insert into rtest_unitfact values ('m', 100.0);
 503 insert into rtest_unitfact values ('cm', 1.0);
 504 insert into rtest_unitfact values ('inch', 2.54);
 505
 506 insert into rtest_comp values ('p1', 'm', 5.0);
 507 insert into rtest_comp values ('p2', 'm', 3.0);
 508 insert into rtest_comp values ('p3', 'cm', 5.0);
 509 insert into rtest_comp values ('p4', 'cm', 15.0);
 510 insert into rtest_comp values ('p5', 'inch', 7.0);
 511 insert into rtest_comp values ('p6', 'inch', 4.4);
 512
 513 select * from rtest_vcomp order by part;
 514
 515 select * from rtest_vcomp where size_in_cm > 10.0 order by size_in_cm using >;
 516
 517 --
 518 -- In addition run the (slightly modified) queries from the
 519 -- programmers manual section on the rule system.
 520 --
 521 CREATE TABLE shoe_data (
 522         shoename   char(10),      -- primary key
 523         sh_avail   integer,       -- available # of pairs
 524         slcolor    char(10),      -- preferred shoelace color
 525         slminlen   float,         -- miminum shoelace length
 526         slmaxlen   float,         -- maximum shoelace length
 527         slunit     char(8)        -- length unit
 528 );
 529
 530 CREATE TABLE shoelace_data (
 531         sl_name    char(10),      -- primary key
 532         sl_avail   integer,       -- available # of pairs
 533         sl_color   char(10),      -- shoelace color
 534         sl_len     float,         -- shoelace length
 535         sl_unit    char(8)        -- length unit
 536 );
 537
 538 CREATE TABLE unit (
 539         un_name    char(8),       -- the primary key
 540         un_fact    float          -- factor to transform to cm
 541 );
 542
 543 CREATE VIEW shoe AS
 544         SELECT sh.shoename,
 545                    sh.sh_avail,
 546                    sh.slcolor,
 547                    sh.slminlen,
 548                    sh.slminlen * un.un_fact AS slminlen_cm,
 549                    sh.slmaxlen,
 550                    sh.slmaxlen * un.un_fact AS slmaxlen_cm,
 551                    sh.slunit
 552           FROM shoe_data sh, unit un
 553          WHERE sh.slunit = un.un_name;
 554
 555 CREATE VIEW shoelace AS
 556         SELECT s.sl_name,
 557                    s.sl_avail,
 558                    s.sl_color,
 559                    s.sl_len,
 560                    s.sl_unit,
 561                    s.sl_len * u.un_fact AS sl_len_cm
 562           FROM shoelace_data s, unit u
 563          WHERE s.sl_unit = u.un_name;
 564
 565 CREATE VIEW shoe_ready AS
 566         SELECT rsh.shoename,
 567                    rsh.sh_avail,
 568                    rsl.sl_name,
 569                    rsl.sl_avail,
 570                    int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail
 571           FROM shoe rsh, shoelace rsl
 572          WHERE rsl.sl_color = rsh.slcolor
 573            AND rsl.sl_len_cm >= rsh.slminlen_cm
 574            AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
 575
 576 INSERT INTO unit VALUES ('cm', 1.0);
 577 INSERT INTO unit VALUES ('m', 100.0);
 578 INSERT INTO unit VALUES ('inch', 2.54);
 579
 580 INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
 581 INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
 582 INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
 583 INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
 584
 585 INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
 586 INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
 587 INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
 588 INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
 589 INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
 590 INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
 591 INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
 592 INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
 593
 594 -- SELECTs in doc
 595 SELECT * FROM shoelace ORDER BY sl_name;
 596 SELECT * FROM shoe_ready WHERE total_avail >= 2 ORDER BY 1;
 597
 598     CREATE TABLE shoelace_log (
 599         sl_name    char(10),      -- shoelace changed
 600         sl_avail   integer,       -- new available value
 601         log_who    name,          -- who did it
 602         log_when   timestamp      -- when
 603     );
 604
 605 -- Want "log_who" to be CURRENT_USER,
 606 -- but that is non-portable for the regression test
 607 -- - thomas 1999-02-21
 608
 609     CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
 610         WHERE NEW.sl_avail != OLD.sl_avail
 611         DO INSERT INTO shoelace_log VALUES (
 612                                         NEW.sl_name,
 613                                         NEW.sl_avail,
 614                                         'Al Bundy',
 615                                         'epoch'
 616                                     );
 617
 618 UPDATE shoelace_data SET sl_avail = 6 WHERE  sl_name = 'sl7';
 619
 620 SELECT * FROM shoelace_log;
 621
 622     CREATE RULE shoelace_ins AS ON INSERT TO shoelace
 623         DO INSTEAD
 624         INSERT INTO shoelace_data VALUES (
 625                NEW.sl_name,
 626                NEW.sl_avail,
 627                NEW.sl_color,
 628                NEW.sl_len,
 629                NEW.sl_unit);
 630
 631     CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
 632         DO INSTEAD
 633         UPDATE shoelace_data SET
 634                sl_name = NEW.sl_name,
 635                sl_avail = NEW.sl_avail,
 636                sl_color = NEW.sl_color,
 637                sl_len = NEW.sl_len,
 638                sl_unit = NEW.sl_unit
 639          WHERE sl_name = OLD.sl_name;
 640
 641     CREATE RULE shoelace_del AS ON DELETE TO shoelace
 642         DO INSTEAD
 643         DELETE FROM shoelace_data
 644          WHERE sl_name = OLD.sl_name;
 645
 646     CREATE TABLE shoelace_arrive (
 647         arr_name    char(10),
 648         arr_quant   integer
 649     );
 650
 651     CREATE TABLE shoelace_ok (
 652         ok_name     char(10),
 653         ok_quant    integer
 654     );
 655
 656     CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
 657         DO INSTEAD
 658         UPDATE shoelace SET
 659                sl_avail = sl_avail + NEW.ok_quant
 660          WHERE sl_name = NEW.ok_name;
 661
 662 INSERT INTO shoelace_arrive VALUES ('sl3', 10);
 663 INSERT INTO shoelace_arrive VALUES ('sl6', 20);
 664 INSERT INTO shoelace_arrive VALUES ('sl8', 20);
 665
 666 SELECT * FROM shoelace ORDER BY sl_name;
 667
 668 insert into shoelace_ok select * from shoelace_arrive;
 669
 670 SELECT * FROM shoelace ORDER BY sl_name;
 671
 672 SELECT * FROM shoelace_log ORDER BY sl_name;
 673
 674     CREATE VIEW shoelace_obsolete AS
 675         SELECT * FROM shoelace WHERE NOT EXISTS
 676             (SELECT shoename FROM shoe WHERE slcolor = sl_color);
 677
 678     CREATE VIEW shoelace_candelete AS
 679         SELECT * FROM shoelace_obsolete WHERE sl_avail = 0;
 680
 681 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
 682 insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
 683
 684 SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm;
 685 SELECT * FROM shoelace_candelete;
 686
 687 DELETE FROM shoelace WHERE EXISTS
 688     (SELECT * FROM shoelace_candelete
 689              WHERE sl_name = shoelace.sl_name);
 690
 691 SELECT * FROM shoelace ORDER BY sl_name;
 692
 693 SELECT * FROM shoe ORDER BY shoename;
 694 SELECT count(*) FROM shoe;
 695
 696
 697 --
 698 -- Simple test of qualified ON INSERT ... this did not work in 7.0 ...
 699 --
 700 create table foo (f1 int);
 701 create table foo2 (f1 int);
 702
 703 create rule foorule as on insert to foo where f1 < 100
 704 do instead nothing;
 705
 706 insert into foo values(1);
 707 insert into foo values(1001);
 708 select * from foo;
 709
 710 drop rule foorule on foo;
 711
 712 -- this should fail because f1 is not exposed for unqualified reference:
 713 create rule foorule as on insert to foo where f1 < 100
 714 do instead insert into foo2 values (f1);
 715 -- this is the correct way:
 716 create rule foorule as on insert to foo where f1 < 100
 717 do instead insert into foo2 values (new.f1);
 718
 719 insert into foo values(2);
 720 insert into foo values(100);
 721
 722 select * from foo;
 723 select * from foo2;
 724
 725 drop rule foorule on foo;
 726 drop table foo;
 727 drop table foo2;
 728
 729
 730 --
 731 -- Test rules containing INSERT ... SELECT, which is a very ugly special
 732 -- case as of 7.1.  Example is based on bug report from Joel Burton.
 733 --
 734 create table pparent (pid int, txt text);
 735 insert into pparent values (1,'parent1');
 736 insert into pparent values (2,'parent2');
 737
 738 create table cchild (pid int, descrip text);
 739 insert into cchild values (1,'descrip1');
 740
 741 create view vview as
 742   select pparent.pid, txt, descrip from
 743     pparent left join cchild using (pid);
 744
 745 create rule rrule as
 746   on update to vview do instead
 747 (
 748   insert into cchild (pid, descrip)
 749     select old.pid, new.descrip where old.descrip isnull;
 750   update cchild set descrip = new.descrip where cchild.pid = old.pid;
 751 );
 752
 753 select * from vview;
 754 update vview set descrip='test1' where pid=1;
 755 select * from vview;
 756 update vview set descrip='test2' where pid=2;
 757 select * from vview;
 758 update vview set descrip='test3' where pid=3;
 759 select * from vview;
 760 select * from cchild;
 761
 762 drop rule rrule on vview;
 763 drop view vview;
 764 drop table pparent;
 765 drop table cchild;
 766
 767
 768 --
 769 -- Check that ruleutils are working
 770 --
 771 SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname;
 772
 773 SELECT tablename, rulename, definition FROM pg_rules
 774         ORDER BY tablename, rulename;
 775
 776 --
 777 -- CREATE OR REPLACE RULE
 778 --
 779
 780 CREATE TABLE ruletest_tbl (a int, b int);
 781 CREATE TABLE ruletest_tbl2 (a int, b int);
 782
 783 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
 784         DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
 785
 786 INSERT INTO ruletest_tbl VALUES (99, 99);
 787
 788 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
 789         DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
 790
 791 INSERT INTO ruletest_tbl VALUES (99, 99);
 792
 793 SELECT * FROM ruletest_tbl2;
 794
 795 -- Check that rewrite rules splitting one INSERT into multiple
 796 -- conditional statements does not disable FK checking.
 797 create table rule_and_refint_t1 (
 798         id1a integer,
 799         id1b integer,
 800
 801         primary key (id1a, id1b)
 802 );
 803
 804 create table rule_and_refint_t2 (
 805         id2a integer,
 806         id2c integer,
 807
 808         primary key (id2a, id2c)
 809 );
 810
 811 create table rule_and_refint_t3 (
 812         id3a integer,
 813         id3b integer,
 814         id3c integer,
 815         data text,
 816
 817         primary key (id3a, id3b, id3c),
 818
 819         foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
 820         foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
 821 );
 822
 823
 824 insert into rule_and_refint_t1 values (1, 11);
 825 insert into rule_and_refint_t1 values (1, 12);
 826 insert into rule_and_refint_t1 values (2, 21);
 827 insert into rule_and_refint_t1 values (2, 22);
 828
 829 insert into rule_and_refint_t2 values (1, 11);
 830 insert into rule_and_refint_t2 values (1, 12);
 831 insert into rule_and_refint_t2 values (2, 21);
 832 insert into rule_and_refint_t2 values (2, 22);
 833
 834 insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
 835 insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
 836 insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
 837 insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
 838 insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
 839 insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
 840
 841 create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
 842         where (exists (select 1 from rule_and_refint_t3
 843                         where (((rule_and_refint_t3.id3a = new.id3a)
 844                         and (rule_and_refint_t3.id3b = new.id3b))
 845                         and (rule_and_refint_t3.id3c = new.id3c))))
 846         do instead update rule_and_refint_t3 set data = new.data
 847         where (((rule_and_refint_t3.id3a = new.id3a)
 848         and (rule_and_refint_t3.id3b = new.id3b))
 849         and (rule_and_refint_t3.id3c = new.id3c));
 850
 851 insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
 852 insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
 853
 854 --
 855 -- check for planner problems with complex inherited UPDATES
 856 --
 857
 858 create table id (id serial primary key, name text);
 859 -- currently, must respecify PKEY for each inherited subtable
 860 create table test_1 (id integer primary key) inherits (id);
 861 create table test_2 (id integer primary key) inherits (id);
 862 create table test_3 (id integer primary key) inherits (id);
 863
 864 insert into test_1 (name) values ('Test 1');
 865 insert into test_1 (name) values ('Test 2');
 866 insert into test_2 (name) values ('Test 3');
 867 insert into test_2 (name) values ('Test 4');
 868 insert into test_3 (name) values ('Test 5');
 869 insert into test_3 (name) values ('Test 6');
 870
 871 create view id_ordered as select * from id order by id;
 872
 873 create rule update_id_ordered as on update to id_ordered
 874         do instead update id set name = new.name where id = old.id;
 875
 876 select * from id_ordered;
 877 update id_ordered set name = 'update 2' where id = 2;
 878 update id_ordered set name = 'update 4' where id = 4;
 879 update id_ordered set name = 'update 5' where id = 5;
 880 select * from id_ordered;
 881
 882 set client_min_messages to warning; -- suppress cascade notices
 883 drop table id cascade;
 884 reset client_min_messages;
 885
 886 --
 887 -- check corner case where an entirely-dummy subplan is created by
 888 -- constraint exclusion
 889 --
 890
 891 create temp table t1 (a integer primary key);
 892
 893 create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1);
 894 create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1);
 895
 896 create rule t1_ins_1 as on insert to t1
 897         where new.a >= 0 and new.a < 10
 898         do instead
 899         insert into t1_1 values (new.a);
 900 create rule t1_ins_2 as on insert to t1
 901         where new.a >= 10 and new.a < 20
 902         do instead
 903         insert into t1_2 values (new.a);
 904
 905 create rule t1_upd_1 as on update to t1
 906         where old.a >= 0 and old.a < 10
 907         do instead
 908         update t1_1 set a = new.a where a = old.a;
 909 create rule t1_upd_2 as on update to t1
 910         where old.a >= 10 and old.a < 20
 911         do instead
 912         update t1_2 set a = new.a where a = old.a;
 913
 914 set constraint_exclusion = on;
 915
 916 insert into t1 select * from generate_series(5,19,1) g;
 917 update t1 set a = 4 where a = 5;
 918
 919 select * from only t1;
 920 select * from only t1_1;
 921 select * from only t1_2;