granicus.if.org Git - postgresql/blob - 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 (
  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 (
  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
  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
  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 where rtest_t2.a = rtest_t3.a;
 222 select * from rtest_v1;
 223
 224 -- updates in a mergejoin
 225 update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a;
 226 select * from rtest_v1;
 227 insert into rtest_v1 select * from rtest_t3;
 228 select * from rtest_v1;
 229 update rtest_t1 set a = a + 10 where b > 30;
 230 select * from rtest_v1;
 231 update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b;
 232 select * from rtest_v1;
 233
 234 --
 235 -- Test for constraint updates/deletes
 236 --
 237 insert into rtest_system values ('orion', 'Linux Jan Wieck');
 238 insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)');
 239 insert into rtest_system values ('neptun', 'Fileserver');
 240
 241 insert into rtest_interface values ('orion', 'eth0');
 242 insert into rtest_interface values ('orion', 'eth1');
 243 insert into rtest_interface values ('notjw', 'eth0');
 244 insert into rtest_interface values ('neptun', 'eth0');
 245
 246 insert into rtest_person values ('jw', 'Jan Wieck');
 247 insert into rtest_person values ('bm', 'Bruce Momjian');
 248
 249 insert into rtest_admin values ('jw', 'orion');
 250 insert into rtest_admin values ('jw', 'notjw');
 251 insert into rtest_admin values ('bm', 'neptun');
 252
 253 update rtest_system set sysname = 'pluto' where sysname = 'neptun';
 254
 255 select * from rtest_interface;
 256 select * from rtest_admin;
 257
 258 update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck';
 259
 260 -- Note: use ORDER BY here to ensure consistent output across all systems.
 261 -- The above UPDATE affects two rows with equal keys, so they could be
 262 -- updated in either order depending on the whim of the local qsort().
 263
 264 select * from rtest_admin order by pname, sysname;
 265
 266 delete from rtest_system where sysname = 'orion';
 267
 268 select * from rtest_interface;
 269 select * from rtest_admin;
 270
 271 --
 272 -- Rule qualification test
 273 --
 274 insert into rtest_emp values ('wiech', '5000.00');
 275 insert into rtest_emp values ('gates', '80000.00');
 276 update rtest_emp set ename = 'wiecx' where ename = 'wiech';
 277 update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx';
 278 update rtest_emp set salary = '7000.00' where ename = 'wieck';
 279 delete from rtest_emp where ename = 'gates';
 280
 281 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 282 insert into rtest_empmass values ('meyer', '4000.00');
 283 insert into rtest_empmass values ('maier', '5000.00');
 284 insert into rtest_empmass values ('mayr', '6000.00');
 285 insert into rtest_emp select * from rtest_empmass;
 286 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 287 update rtest_empmass set salary = salary + '1000.00';
 288 update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename;
 289 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
 290 delete from rtest_emp where 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
 293 --
 294 -- Multiple cascaded qualified instead rule test
 295 --
 296 insert into rtest_t4 values (1, 'Record should go to rtest_t4');
 297 insert into rtest_t4 values (2, 'Record should go to rtest_t4');
 298 insert into rtest_t4 values (10, 'Record should go to rtest_t5');
 299 insert into rtest_t4 values (15, 'Record should go to rtest_t5');
 300 insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7');
 301 insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6');
 302 insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8');
 303 insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8');
 304 insert into rtest_t4 values (30, 'Record should go to rtest_t4');
 305 insert into rtest_t4 values (40, 'Record should go to rtest_t4');
 306
 307 select * from rtest_t4;
 308 select * from rtest_t5;
 309 select * from rtest_t6;
 310 select * from rtest_t7;
 311 select * from rtest_t8;
 312
 313 delete from rtest_t4;
 314 delete from rtest_t5;
 315 delete from rtest_t6;
 316 delete from rtest_t7;
 317 delete from rtest_t8;
 318
 319 insert into rtest_t9 values (1, 'Record should go to rtest_t4');
 320 insert into rtest_t9 values (2, 'Record should go to rtest_t4');
 321 insert into rtest_t9 values (10, 'Record should go to rtest_t5');
 322 insert into rtest_t9 values (15, 'Record should go to rtest_t5');
 323 insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7');
 324 insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6');
 325 insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8');
 326 insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8');
 327 insert into rtest_t9 values (30, 'Record should go to rtest_t4');
 328 insert into rtest_t9 values (40, 'Record should go to rtest_t4');
 329
 330 insert into rtest_t4 select * from rtest_t9 where a < 20;
 331
 332 select * from rtest_t4;
 333 select * from rtest_t5;
 334 select * from rtest_t6;
 335 select * from rtest_t7;
 336 select * from rtest_t8;
 337
 338 insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8';
 339
 340 select * from rtest_t4;
 341 select * from rtest_t5;
 342 select * from rtest_t6;
 343 select * from rtest_t7;
 344 select * from rtest_t8;
 345
 346 insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40);
 347
 348 select * from rtest_t4;
 349 select * from rtest_t5;
 350 select * from rtest_t6;
 351 select * from rtest_t7;
 352 select * from rtest_t8;
 353
 354 --
 355 -- Check that the ordering of rules fired is correct
 356 --
 357 insert into rtest_order1 values (1);
 358 select * from rtest_order2;
 359
 360 --
 361 -- Check if instead nothing w/without qualification works
 362 --
 363 insert into rtest_nothn1 values (1, 'want this');
 364 insert into rtest_nothn1 values (2, 'want this');
 365 insert into rtest_nothn1 values (10, 'don''t want this');
 366 insert into rtest_nothn1 values (19, 'don''t want this');
 367 insert into rtest_nothn1 values (20, 'want this');
 368 insert into rtest_nothn1 values (29, 'want this');
 369 insert into rtest_nothn1 values (30, 'don''t want this');
 370 insert into rtest_nothn1 values (39, 'don''t want this');
 371 insert into rtest_nothn1 values (40, 'want this');
 372 insert into rtest_nothn1 values (50, 'want this');
 373 insert into rtest_nothn1 values (60, 'want this');
 374
 375 select * from rtest_nothn1;
 376
 377 insert into rtest_nothn2 values (10, 'too small');
 378 insert into rtest_nothn2 values (50, 'too small');
 379 insert into rtest_nothn2 values (100, 'OK');
 380 insert into rtest_nothn2 values (200, 'OK');
 381
 382 select * from rtest_nothn2;
 383 select * from rtest_nothn3;
 384
 385 delete from rtest_nothn1;
 386 delete from rtest_nothn2;
 387 delete from rtest_nothn3;
 388
 389 insert into rtest_nothn4 values (1, 'want this');
 390 insert into rtest_nothn4 values (2, 'want this');
 391 insert into rtest_nothn4 values (10, 'don''t want this');
 392 insert into rtest_nothn4 values (19, 'don''t want this');
 393 insert into rtest_nothn4 values (20, 'want this');
 394 insert into rtest_nothn4 values (29, 'want this');
 395 insert into rtest_nothn4 values (30, 'don''t want this');
 396 insert into rtest_nothn4 values (39, 'don''t want this');
 397 insert into rtest_nothn4 values (40, 'want this');
 398 insert into rtest_nothn4 values (50, 'want this');
 399 insert into rtest_nothn4 values (60, 'want this');
 400
 401 insert into rtest_nothn1 select * from rtest_nothn4;
 402
 403 select * from rtest_nothn1;
 404
 405 delete from rtest_nothn4;
 406
 407 insert into rtest_nothn4 values (10, 'too small');
 408 insert into rtest_nothn4 values (50, 'too small');
 409 insert into rtest_nothn4 values (100, 'OK');
 410 insert into rtest_nothn4 values (200, 'OK');
 411
 412 insert into rtest_nothn2 select * from rtest_nothn4;
 413
 414 select * from rtest_nothn2;
 415 select * from rtest_nothn3;
 416
 417 create table rtest_view1 (a int4, b text, v bool);
 418 create table rtest_view2 (a int4);
 419 create table rtest_view3 (a int4, b text);
 420 create table rtest_view4 (a int4, b text, c int4);
 421 create view rtest_vview1 as select a, b from rtest_view1 X
 422         where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
 423 create view rtest_vview2 as select a, b from rtest_view1 where v;
 424 create view rtest_vview3 as select a, b from rtest_vview2 X
 425         where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
 426 create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount
 427         from rtest_view1 X, rtest_view2 Y
 428         where X.a = Y.a
 429         group by X.a, X.b;
 430 create function rtest_viewfunc1(int4) returns int4 as
 431         'select count(*)::int4 from rtest_view2 where a = $1'
 432         language 'sql';
 433 create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount
 434         from rtest_view1;
 435
 436 insert into rtest_view1 values (1, 'item 1', 't');
 437 insert into rtest_view1 values (2, 'item 2', 't');
 438 insert into rtest_view1 values (3, 'item 3', 't');
 439 insert into rtest_view1 values (4, 'item 4', 'f');
 440 insert into rtest_view1 values (5, 'item 5', 't');
 441 insert into rtest_view1 values (6, 'item 6', 'f');
 442 insert into rtest_view1 values (7, 'item 7', 't');
 443 insert into rtest_view1 values (8, 'item 8', 't');
 444
 445 insert into rtest_view2 values (2);
 446 insert into rtest_view2 values (2);
 447 insert into rtest_view2 values (4);
 448 insert into rtest_view2 values (5);
 449 insert into rtest_view2 values (7);
 450 insert into rtest_view2 values (7);
 451 insert into rtest_view2 values (7);
 452 insert into rtest_view2 values (7);
 453
 454 select * from rtest_vview1;
 455 select * from rtest_vview2;
 456 select * from rtest_vview3;
 457 select * from rtest_vview4 order by a, b;
 458 select * from rtest_vview5;
 459
 460 insert into rtest_view3 select * from rtest_vview1 where a < 7;
 461 select * from rtest_view3;
 462 delete from rtest_view3;
 463
 464 insert into rtest_view3 select * from rtest_vview2 where a != 5 and b !~ '2';
 465 select * from rtest_view3;
 466 delete from rtest_view3;
 467
 468 insert into rtest_view3 select * from rtest_vview3;
 469 select * from rtest_view3;
 470 delete from rtest_view3;
 471
 472 insert into rtest_view4 select * from rtest_vview4 where 3 > refcount;
 473 select * from rtest_view4 order by a, b;
 474 delete from rtest_view4;
 475
 476 insert into rtest_view4 select * from rtest_vview5 where a > 2 and refcount = 0;
 477 select * from rtest_view4;
 478 delete from rtest_view4;
 479 --
 480 -- Test for computations in views
 481 --
 482 create table rtest_comp (
 483         part    text,
 484         unit    char(4),
 485         size    float
 486 );
 487
 488
 489 create table rtest_unitfact (
 490         unit    char(4),
 491         factor  float
 492 );
 493
 494 create view rtest_vcomp as
 495         select X.part, (X.size * Y.factor) as size_in_cm
 496                         from rtest_comp X, rtest_unitfact Y
 497                         where X.unit = Y.unit;
 498
 499
 500 insert into rtest_unitfact values ('m', 100.0);
 501 insert into rtest_unitfact values ('cm', 1.0);
 502 insert into rtest_unitfact values ('inch', 2.54);
 503
 504 insert into rtest_comp values ('p1', 'm', 5.0);
 505 insert into rtest_comp values ('p2', 'm', 3.0);
 506 insert into rtest_comp values ('p3', 'cm', 5.0);
 507 insert into rtest_comp values ('p4', 'cm', 15.0);
 508 insert into rtest_comp values ('p5', 'inch', 7.0);
 509 insert into rtest_comp values ('p6', 'inch', 4.4);
 510
 511 select * from rtest_vcomp order by part;
 512
 513 select * from rtest_vcomp where size_in_cm > 10.0 order by size_in_cm using >;
 514
 515 --
 516 -- In addition run the (slightly modified) queries from the
 517 -- programmers manual section on the rule system.
 518 --
 519 CREATE TABLE shoe_data (
 520         shoename   char(10),      -- primary key
 521         sh_avail   integer,       -- available # of pairs
 522         slcolor    char(10),      -- preferred shoelace color
 523         slminlen   float,         -- miminum shoelace length
 524         slmaxlen   float,         -- maximum shoelace length
 525         slunit     char(8)        -- length unit
 526 );
 527
 528 CREATE TABLE shoelace_data (
 529         sl_name    char(10),      -- primary key
 530         sl_avail   integer,       -- available # of pairs
 531         sl_color   char(10),      -- shoelace color
 532         sl_len     float,         -- shoelace length
 533         sl_unit    char(8)        -- length unit
 534 );
 535
 536 CREATE TABLE unit (
 537         un_name    char(8),       -- the primary key
 538         un_fact    float          -- factor to transform to cm
 539 );
 540
 541 CREATE VIEW shoe AS
 542         SELECT sh.shoename,
 543                    sh.sh_avail,
 544                    sh.slcolor,
 545                    sh.slminlen,
 546                    sh.slminlen * un.un_fact AS slminlen_cm,
 547                    sh.slmaxlen,
 548                    sh.slmaxlen * un.un_fact AS slmaxlen_cm,
 549                    sh.slunit
 550           FROM shoe_data sh, unit un
 551          WHERE sh.slunit = un.un_name;
 552
 553 CREATE VIEW shoelace AS
 554         SELECT s.sl_name,
 555                    s.sl_avail,
 556                    s.sl_color,
 557                    s.sl_len,
 558                    s.sl_unit,
 559                    s.sl_len * u.un_fact AS sl_len_cm
 560           FROM shoelace_data s, unit u
 561          WHERE s.sl_unit = u.un_name;
 562
 563 CREATE VIEW shoe_ready AS
 564         SELECT rsh.shoename,
 565                    rsh.sh_avail,
 566                    rsl.sl_name,
 567                    rsl.sl_avail,
 568                    int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail
 569           FROM shoe rsh, shoelace rsl
 570          WHERE rsl.sl_color = rsh.slcolor
 571            AND rsl.sl_len_cm >= rsh.slminlen_cm
 572            AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
 573
 574 INSERT INTO unit VALUES ('cm', 1.0);
 575 INSERT INTO unit VALUES ('m', 100.0);
 576 INSERT INTO unit VALUES ('inch', 2.54);
 577
 578 INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
 579 INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
 580 INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
 581 INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
 582
 583 INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
 584 INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
 585 INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
 586 INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
 587 INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
 588 INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
 589 INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
 590 INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
 591
 592 -- SELECTs in doc
 593 SELECT * FROM shoelace ORDER BY sl_name;
 594 SELECT * FROM shoe_ready WHERE total_avail >= 2 ORDER BY 1;
 595
 596     CREATE TABLE shoelace_log (
 597         sl_name    char(10),      -- shoelace changed
 598         sl_avail   integer,       -- new available value
 599         log_who    name,          -- who did it
 600         log_when   timestamp      -- when
 601     );
 602
 603 -- Want "log_who" to be CURRENT_USER,
 604 -- but that is non-portable for the regression test
 605 -- - thomas 1999-02-21
 606
 607     CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
 608         WHERE NEW.sl_avail != OLD.sl_avail
 609         DO INSERT INTO shoelace_log VALUES (
 610                                         NEW.sl_name,
 611                                         NEW.sl_avail,
 612                                         'Al Bundy',
 613                                         'epoch'
 614                                     );
 615
 616 UPDATE shoelace_data SET sl_avail = 6 WHERE  sl_name = 'sl7';
 617
 618 SELECT * FROM shoelace_log;
 619
 620     CREATE RULE shoelace_ins AS ON INSERT TO shoelace
 621         DO INSTEAD
 622         INSERT INTO shoelace_data VALUES (
 623                NEW.sl_name,
 624                NEW.sl_avail,
 625                NEW.sl_color,
 626                NEW.sl_len,
 627                NEW.sl_unit);
 628
 629     CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
 630         DO INSTEAD
 631         UPDATE shoelace_data SET
 632                sl_name = NEW.sl_name,
 633                sl_avail = NEW.sl_avail,
 634                sl_color = NEW.sl_color,
 635                sl_len = NEW.sl_len,
 636                sl_unit = NEW.sl_unit
 637          WHERE sl_name = OLD.sl_name;
 638
 639     CREATE RULE shoelace_del AS ON DELETE TO shoelace
 640         DO INSTEAD
 641         DELETE FROM shoelace_data
 642          WHERE sl_name = OLD.sl_name;
 643
 644     CREATE TABLE shoelace_arrive (
 645         arr_name    char(10),
 646         arr_quant   integer
 647     );
 648
 649     CREATE TABLE shoelace_ok (
 650         ok_name     char(10),
 651         ok_quant    integer
 652     );
 653
 654     CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
 655         DO INSTEAD
 656         UPDATE shoelace SET
 657                sl_avail = sl_avail + NEW.ok_quant
 658          WHERE sl_name = NEW.ok_name;
 659
 660 INSERT INTO shoelace_arrive VALUES ('sl3', 10);
 661 INSERT INTO shoelace_arrive VALUES ('sl6', 20);
 662 INSERT INTO shoelace_arrive VALUES ('sl8', 20);
 663
 664 SELECT * FROM shoelace ORDER BY sl_name;
 665
 666 insert into shoelace_ok select * from shoelace_arrive;
 667
 668 SELECT * FROM shoelace ORDER BY sl_name;
 669
 670 SELECT * FROM shoelace_log ORDER BY sl_name;
 671
 672     CREATE VIEW shoelace_obsolete AS
 673         SELECT * FROM shoelace WHERE NOT EXISTS
 674             (SELECT shoename FROM shoe WHERE slcolor = sl_color);
 675
 676     CREATE VIEW shoelace_candelete AS
 677         SELECT * FROM shoelace_obsolete WHERE sl_avail = 0;
 678
 679 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
 680 insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
 681
 682 SELECT * FROM shoelace_obsolete;
 683 SELECT * FROM shoelace_candelete;
 684
 685 DELETE FROM shoelace WHERE EXISTS
 686     (SELECT * FROM shoelace_candelete
 687              WHERE sl_name = shoelace.sl_name);
 688
 689 SELECT * FROM shoelace ORDER BY sl_name;
 690
 691 SELECT * FROM shoe ORDER BY shoename;
 692 SELECT count(*) FROM shoe;
 693
 694
 695 --
 696 -- Simple test of qualified ON INSERT ... this did not work in 7.0 ...
 697 --
 698 create table foo (f1 int);
 699 create table foo2 (f1 int);
 700
 701 create rule foorule as on insert to foo where f1 < 100
 702 do instead nothing;
 703
 704 insert into foo values(1);
 705 insert into foo values(1001);
 706 select * from foo;
 707
 708 drop rule foorule on foo;
 709
 710 -- this should fail because f1 is not exposed for unqualified reference:
 711 create rule foorule as on insert to foo where f1 < 100
 712 do instead insert into foo2 values (f1);
 713 -- this is the correct way:
 714 create rule foorule as on insert to foo where f1 < 100
 715 do instead insert into foo2 values (new.f1);
 716
 717 insert into foo values(2);
 718 insert into foo values(100);
 719
 720 select * from foo;
 721 select * from foo2;
 722
 723 drop rule foorule on foo;
 724 drop table foo;
 725 drop table foo2;
 726
 727
 728 --
 729 -- Test rules containing INSERT ... SELECT, which is a very ugly special
 730 -- case as of 7.1.  Example is based on bug report from Joel Burton.
 731 --
 732 create table pparent (pid int, txt text);
 733 insert into pparent values (1,'parent1');
 734 insert into pparent values (2,'parent2');
 735
 736 create table cchild (pid int, descrip text);
 737 insert into cchild values (1,'descrip1');
 738
 739 create view vview as
 740   select pparent.pid, txt, descrip from
 741     pparent left join cchild using (pid);
 742
 743 create rule rrule as
 744   on update to vview do instead
 745 (
 746   insert into cchild (pid, descrip)
 747     select old.pid, new.descrip where old.descrip isnull;
 748   update cchild set descrip = new.descrip where cchild.pid = old.pid;
 749 );
 750
 751 select * from vview;
 752 update vview set descrip='test1' where pid=1;
 753 select * from vview;
 754 update vview set descrip='test2' where pid=2;
 755 select * from vview;
 756 update vview set descrip='test3' where pid=3;
 757 select * from vview;
 758 select * from cchild;
 759
 760 drop rule rrule on vview;
 761 drop view vview;
 762 drop table pparent;
 763 drop table cchild;
 764
 765
 766 --
 767 -- Check that ruleutils are working
 768 --
 769 SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname;
 770
 771 SELECT tablename, rulename, definition FROM pg_rules
 772         ORDER BY tablename, rulename;
 773
 774 --
 775 -- CREATE OR REPLACE RULE
 776 --
 777
 778 CREATE TABLE ruletest_tbl (a int, b int);
 779 CREATE TABLE ruletest_tbl2 (a int, b int);
 780
 781 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
 782         DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
 783
 784 INSERT INTO ruletest_tbl VALUES (99, 99);
 785
 786 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
 787         DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
 788
 789 INSERT INTO ruletest_tbl VALUES (99, 99);
 790
 791 SELECT * FROM ruletest_tbl2;
 792
 793 -- Check that rewrite rules splitting one INSERT into multiple
 794 -- conditional statements does not disable FK checking.
 795 create table rule_and_refint_t1 (
 796         id1a integer,
 797         id1b integer,
 798
 799         primary key (id1a, id1b)
 800 );
 801
 802 create table rule_and_refint_t2 (
 803         id2a integer,
 804         id2c integer,
 805
 806         primary key (id2a, id2c)
 807 );
 808
 809 create table rule_and_refint_t3 (
 810         id3a integer,
 811         id3b integer,
 812         id3c integer,
 813         data text,
 814
 815         primary key (id3a, id3b, id3c),
 816
 817         foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
 818         foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
 819 );
 820
 821
 822 insert into rule_and_refint_t1 values (1, 11);
 823 insert into rule_and_refint_t1 values (1, 12);
 824 insert into rule_and_refint_t1 values (2, 21);
 825 insert into rule_and_refint_t1 values (2, 22);
 826
 827 insert into rule_and_refint_t2 values (1, 11);
 828 insert into rule_and_refint_t2 values (1, 12);
 829 insert into rule_and_refint_t2 values (2, 21);
 830 insert into rule_and_refint_t2 values (2, 22);
 831
 832 insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
 833 insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
 834 insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
 835 insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
 836 insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
 837 insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
 838
 839 create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
 840         where (exists (select 1 from rule_and_refint_t3
 841                         where (((rule_and_refint_t3.id3a = new.id3a)
 842                         and (rule_and_refint_t3.id3b = new.id3b))
 843                         and (rule_and_refint_t3.id3c = new.id3c))))
 844         do instead update rule_and_refint_t3 set data = new.data
 845         where (((rule_and_refint_t3.id3a = new.id3a)
 846         and (rule_and_refint_t3.id3b = new.id3b))
 847         and (rule_and_refint_t3.id3c = new.id3c));
 848
 849 insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
 850 insert into rule_and_refint_t3 values (1, 13, 11, 'row8');