rules.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

 a | b  
---+----
 2 | 99
 1 | 88
 3 | 88
(3 rows)

delete from rtest_v1;
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;
 a | b  
---+----
 1 | 31
 2 | 32
 3 | 33
(3 rows)

-- updates in a mergejoin
update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.a = rtest_t2.a;
select * from rtest_v1;
 a | b  
---+----
 1 | 21
 2 | 22
 3 | 23
(3 rows)

insert into rtest_v1 select * from rtest_t3;
select * from rtest_v1;
 a | b  
---+----
 1 | 21
 2 | 22
 3 | 23
 1 | 31
 2 | 32
 3 | 33
 4 | 34
 5 | 35
(8 rows)

update rtest_t1 set a = a + 10 where b > 30;
select * from rtest_v1;
 a  | b  
----+----
  1 | 21
  2 | 22
  3 | 23
 11 | 31
 12 | 32
 13 | 33
 14 | 34
 15 | 35
(8 rows)

update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b;
select * from rtest_v1;
 a  | b  
----+----
  1 | 21
  2 | 22
  3 | 23
 21 | 31
 22 | 32
 23 | 33
 24 | 34
 25 | 35
(8 rows)

--
-- 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;
 sysname | ifname 
---------+--------
 orion   | eth0
 orion   | eth1
 notjw   | eth0
 pluto   | eth0
(4 rows)

select * from rtest_admin;
 pname | sysname 
-------+---------
 jw    | orion
 jw    | notjw
 bm    | pluto
(3 rows)

update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck';
-- Note: use ORDER BY here to ensure consistent output across all systems.
-- The above UPDATE affects two rows with equal keys, so they could be
-- updated in either order depending on the whim of the local qsort().
select * from rtest_admin order by pname, sysname;
 pname  | sysname 
--------+---------
 bm     | pluto
 jwieck | notjw
 jwieck | orion
(3 rows)

delete from rtest_system where sysname = 'orion';
select * from rtest_interface;
 sysname | ifname 
---------+--------
 notjw   | eth0
 pluto   | eth0
(2 rows)

select * from rtest_admin;
 pname  | sysname 
--------+---------
 bm     | pluto
 jwieck | notjw
(2 rows)

--
-- 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 order by ename, action, newsal;
        ename         | matches user |   action   |   newsal   |   oldsal   
----------------------+--------------+------------+------------+------------
 gates                | t            | fired      |      $0.00 | $80,000.00
 gates                | t            | hired      | $80,000.00 |      $0.00
 wiech                | t            | hired      |  $5,000.00 |      $0.00
 wieck                | t            | honored    |  $6,000.00 |  $5,000.00
 wieck                | t            | honored    |  $7,000.00 |  $6,000.00
(5 rows)

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 order by ename, action, newsal;
        ename         | matches user |   action   |   newsal   |   oldsal   
----------------------+--------------+------------+------------+------------
 gates                | t            | fired      |      $0.00 | $80,000.00
 gates                | t            | hired      | $80,000.00 |      $0.00
 maier                | t            | hired      |  $5,000.00 |      $0.00
 mayr                 | t            | hired      |  $6,000.00 |      $0.00
 meyer                | t            | hired      |  $4,000.00 |      $0.00
 wiech                | t            | hired      |  $5,000.00 |      $0.00
 wieck                | t            | honored    |  $6,000.00 |  $5,000.00
 wieck                | t            | honored    |  $7,000.00 |  $6,000.00
(8 rows)

update rtest_empmass set salary = salary + '1000.00';
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;
        ename         | matches user |   action   |   newsal   |   oldsal   
----------------------+--------------+------------+------------+------------
 gates                | t            | fired      |      $0.00 | $80,000.00
 gates                | t            | hired      | $80,000.00 |      $0.00
 maier                | t            | hired      |  $5,000.00 |      $0.00
 maier                | t            | honored    |  $6,000.00 |  $5,000.00
 mayr                 | t            | hired      |  $6,000.00 |      $0.00
 mayr                 | t            | honored    |  $7,000.00 |  $6,000.00
 meyer                | t            | hired      |  $4,000.00 |      $0.00
 meyer                | t            | honored    |  $5,000.00 |  $4,000.00
 wiech                | t            | hired      |  $5,000.00 |      $0.00
 wieck                | t            | honored    |  $6,000.00 |  $5,000.00
 wieck                | t            | honored    |  $7,000.00 |  $6,000.00
(11 rows)

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;
        ename         | matches user |   action   |   newsal   |   oldsal   
----------------------+--------------+------------+------------+------------
 gates                | t            | fired      |      $0.00 | $80,000.00
 gates                | t            | hired      | $80,000.00 |      $0.00
 maier                | t            | fired      |      $0.00 |  $6,000.00
 maier                | t            | hired      |  $5,000.00 |      $0.00
 maier                | t            | honored    |  $6,000.00 |  $5,000.00
 mayr                 | t            | fired      |      $0.00 |  $7,000.00
 mayr                 | t            | hired      |  $6,000.00 |      $0.00
 mayr                 | t            | honored    |  $7,000.00 |  $6,000.00
 meyer                | t            | fired      |      $0.00 |  $5,000.00
 meyer                | t            | hired      |  $4,000.00 |      $0.00
 meyer                | t            | honored    |  $5,000.00 |  $4,000.00
 wiech                | t            | hired      |  $5,000.00 |      $0.00
 wieck                | t            | honored    |  $6,000.00 |  $5,000.00
 wieck                | t            | honored    |  $7,000.00 |  $6,000.00
(14 rows)

--
-- 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;
 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)

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)

select * from rtest_t6;
 a  |                  b                  
----+-------------------------------------
 20 | Record should go to rtest_t4 and t6
(1 row)

select * from rtest_t7;
 a  |                  b                  
----+-------------------------------------
 19 | Record should go to rtest_t5 and t7
(1 row)

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)

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');