rules.out

来自「PostgreSQL7.4.6 for Linux」· OUT 代码 · 共 1,399 行 · 第 1/5 页

 19 | Record should go to rtest_t5 and t7
(1 row)

select * from rtest_t8;
 a | b 
---+---
(0 rows)

insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8';
select * from rtest_t4;
 a  |                  b                  
----+-------------------------------------
  1 | Record should go to rtest_t4
  2 | Record should go to rtest_t4
 26 | Record should go to rtest_t4 and t8
 28 | Record should go to rtest_t4 and t8
(4 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 
---+---
(0 rows)

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)

insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40);
select * from rtest_t4;
 a  |                  b                  
----+-------------------------------------
  1 | Record should go to rtest_t4
  2 | Record should go to rtest_t4
 26 | Record should go to rtest_t4 and t8
 28 | Record should go to rtest_t4 and t8
 21 | Record should go to rtest_t4 and t6
 31 | Record should go to rtest_t4
 41 | 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                  
----+-------------------------------------
 21 | 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)

--
-- Check that the ordering of rules fired is correct
--
insert into rtest_order1 values (1);
select * from rtest_order2;
 a | b |              c               
---+---+------------------------------
 1 | 1 | rule 1 - this should run 1st
 1 | 2 | rule 2 - this should run 2nd
 1 | 3 | rule 3 - this should run 3rd
 1 | 4 | rule 4 - this should run 4th
(4 rows)

--
-- Check if instead nothing w/without qualification works
--
insert into rtest_nothn1 values (1, 'want this');
insert into rtest_nothn1 values (2, 'want this');
insert into rtest_nothn1 values (10, 'don''t want this');
insert into rtest_nothn1 values (19, 'don''t want this');
insert into rtest_nothn1 values (20, 'want this');
insert into rtest_nothn1 values (29, 'want this');
insert into rtest_nothn1 values (30, 'don''t want this');
insert into rtest_nothn1 values (39, 'don''t want this');
insert into rtest_nothn1 values (40, 'want this');
insert into rtest_nothn1 values (50, 'want this');
insert into rtest_nothn1 values (60, 'want this');
select * from rtest_nothn1;
 a  |     b     
----+-----------
  1 | want this
  2 | want this
 20 | want this
 29 | want this
 40 | want this
 50 | want this
 60 | want this
(7 rows)

insert into rtest_nothn2 values (10, 'too small');
insert into rtest_nothn2 values (50, 'too small');
insert into rtest_nothn2 values (100, 'OK');
insert into rtest_nothn2 values (200, 'OK');
select * from rtest_nothn2;
 a | b 
---+---
(0 rows)

select * from rtest_nothn3;
  a  | b  
-----+----
 100 | OK
 200 | OK
(2 rows)

delete from rtest_nothn1;
delete from rtest_nothn2;
delete from rtest_nothn3;
insert into rtest_nothn4 values (1, 'want this');
insert into rtest_nothn4 values (2, 'want this');
insert into rtest_nothn4 values (10, 'don''t want this');
insert into rtest_nothn4 values (19, 'don''t want this');
insert into rtest_nothn4 values (20, 'want this');
insert into rtest_nothn4 values (29, 'want this');
insert into rtest_nothn4 values (30, 'don''t want this');
insert into rtest_nothn4 values (39, 'don''t want this');
insert into rtest_nothn4 values (40, 'want this');
insert into rtest_nothn4 values (50, 'want this');
insert into rtest_nothn4 values (60, 'want this');
insert into rtest_nothn1 select * from rtest_nothn4;
select * from rtest_nothn1;
 a  |     b     
----+-----------
  1 | want this
  2 | want this
 20 | want this
 29 | want this
 40 | want this
 50 | want this
 60 | want this
(7 rows)

delete from rtest_nothn4;
insert into rtest_nothn4 values (10, 'too small');
insert into rtest_nothn4 values (50, 'too small');
insert into rtest_nothn4 values (100, 'OK');
insert into rtest_nothn4 values (200, 'OK');
insert into rtest_nothn2 select * from rtest_nothn4;
select * from rtest_nothn2;
 a | b 
---+---
(0 rows)

select * from rtest_nothn3;
  a  | b  
-----+----
 100 | OK
 200 | OK
(2 rows)

create table rtest_view1 (a int4, b text, v bool);
create table rtest_view2 (a int4);
create table rtest_view3 (a int4, b text);
create table rtest_view4 (a int4, b text, c int4);
create view rtest_vview1 as select a, b from rtest_view1 X 
	where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
create view rtest_vview2 as select a, b from rtest_view1 where v;
create view rtest_vview3 as select a, b from rtest_vview2 X
	where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount
	from rtest_view1 X, rtest_view2 Y
	where X.a = Y.a
	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';
create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount
	from rtest_view1;
insert into rtest_view1 values (1, 'item 1', 't');
insert into rtest_view1 values (2, 'item 2', 't');
insert into rtest_view1 values (3, 'item 3', 't');
insert into rtest_view1 values (4, 'item 4', 'f');
insert into rtest_view1 values (5, 'item 5', 't');
insert into rtest_view1 values (6, 'item 6', 'f');
insert into rtest_view1 values (7, 'item 7', 't');
insert into rtest_view1 values (8, 'item 8', 't');
insert into rtest_view2 values (2);
insert into rtest_view2 values (2);
insert into rtest_view2 values (4);
insert into rtest_view2 values (5);
insert into rtest_view2 values (7);
insert into rtest_view2 values (7);
insert into rtest_view2 values (7);
insert into rtest_view2 values (7);
select * from rtest_vview1;
 a |   b    
---+--------
 2 | item 2
 4 | item 4
 5 | item 5
 7 | item 7
(4 rows)

select * from rtest_vview2;
 a |   b    
---+--------
 1 | item 1
 2 | item 2
 3 | item 3
 5 | item 5
 7 | item 7
 8 | item 8
(6 rows)

select * from rtest_vview3;
 a |   b    
---+--------
 2 | item 2
 5 | item 5
 7 | item 7
(3 rows)

select * from rtest_vview4 order by a, b;
 a |   b    | refcount 
---+--------+----------
 2 | item 2 |        2
 4 | item 4 |        1
 5 | item 5 |        1
 7 | item 7 |        4
(4 rows)

select * from rtest_vview5;
 a |   b    | refcount 
---+--------+----------
 1 | item 1 |        0
 2 | item 2 |        2
 3 | item 3 |        0
 4 | item 4 |        1
 5 | item 5 |        1
 6 | item 6 |        0
 7 | item 7 |        4
 8 | item 8 |        0
(8 rows)

insert into rtest_view3 select * from rtest_vview1 where a < 7;
select * from rtest_view3;
 a |   b    
---+--------
 2 | item 2
 4 | item 4
 5 | item 5
(3 rows)

delete from rtest_view3;
insert into rtest_view3 select * from rtest_vview2 where a != 5 and b !~ '2';
select * from rtest_view3;