Views Triggers and Recursive Queries 1 Views and

Views, Triggers and Recursive Queries 1

Views and Recursive Queries • Assume we have a relation Par(child, parent). 1. Write a view that contains all pairs of siblings. 2. Write a view that contains all pairs of direct cousins. 3. Write a query that computes all (direct or indirect) cousins. 2

Solution (Part 1) CREATE VIEW Siblings AS SELECT P 1. child as sib 1, P 2. child as sib 2 FROM Par P 1, Par P 2 WHERE P 1. parent = P 2. parent and P 1. child<>P 2. child 3

Solution (Part 2) CREATE VIEW DCousins AS SELECT P 1. child as cous 1, P 2. child as cous 2 FROM Par P 1, Par P 2, Siblings S WHERE P 1. parent = sib 1 and P 2. parent = sib 2 4

Solution (Part 3) WITH Recursive Cousins(C 1, C 2) AS (SELECT * FROM DCousins) UNION (SELECT P 1. child, P 2. child FROM Par P 1, Par P 2, Cousins C WHERE P 1. parent = c 1 and P 2. parent = c 2) SELECT * FROM Cousins 5

Views 1. create table a(col 1 integer, col 2 integer); 2. create view. A as select col 1 from a where col 1>col 2; 3. insert into a values(1, 2); 4. insert into a values(13, 7); 5. insert into a values(16, 19); 6. select * from view. A; 7. update view. A set col 1 = col 1 + 2 where col 1<15 8. select * from a; 9. select * from view. A; • What will be returned on line 6? On line 8? On line 9? 6

Solution • Line 6: 13 • Line 8: (1, 2), (15, 7), (16, 19) • Line 9: 15, 3 7

Triggers create or replace function mystery() returns trigger as $$ begin Solution: new. a : = (new. a + old. a)/2; (2, 5), (3, 6), (6, 3) new. b : = (new. b + old. b)/2; return new; end $$ language plpgsql; create trigger mysterytrig before update on nums for each row execute procedure mystery(); update nums set a = a+2 where b>4; select * from nums; nums a b 1 5 2 6 6 3 8

Triggers create or replace function mystery() returns trigger as $$ begin Solution: new. a : = (new. a + old. a)/2; (3, 5), (4, 6), (6, 3) new. b : = (new. b + old. b)/2; return new; end $$ language plpgsql; create trigger mysterytrig after update on nums for each row execute procedure mystery(); update nums set a = a+2 where b>4; select * from nums; nums a b 1 5 2 6 6 3 9

Triggers create or replace function enigma() returns trigger as $$ begin Solution: insert into second values(new. A * new. A); First: 10, 21, 5, 2 Second: 27, 4 update second set b=b+1 where b>12; return null; first end a $$ language plpgsql; 10 create trigger enigmatrig after insert on first for each row execute procedure enigma(); insert into first values(5); insert into first values(2); select * from first; select * from second; 21 second b 10

Triggers create or replace function enigma() returns trigger as $$ begin Solution: insert into second values(new. A * new. A); First: 10, 21 Second: 27, 4 update second set b=b+1 where b>12; return null; first end a $$ language plpgsql; 10 create trigger enigmatrig before insert on first for each row execute procedure enigma(); insert into first values(5); insert into first values(2); select * from first; select * from second; 21 second b 11

Triggers create or replace function enigma() returns trigger as $$ begin Solution: insert into second values(new. A * new. A); First: 10, 21, 5, 2 Second: 27, 4 update second set b=b+1 where b>12; return new; first end a $$ language plpgsql; 10 create trigger enigmatrig before insert on first for each row execute procedure enigma(); insert into first values(5); insert into first values(2); select * from first; select * from second; 21 second b 12

Triggers create or replace function enigma() returns trigger as $$ begin Solution: insert into second values(new. A * new. A); First: 10, 21 Second: 27, 4 update second set b=b+1 where b>12; return old; first end a $$ language plpgsql; 10 create trigger enigmatrig before insert on first for each row execute procedure enigma(); insert into first values(5); insert into first values(2); select * from first; select * from second; 21 second b 13

Triggers create or replace function enigma() returns trigger as $$ Solution: The answer is begin order dependent. First will insert into second values(old. A * new. A); have 4, 5. We will either get: update second set b=b+1; Second with 6, 11 or return old; Second with 5, 12 end $$ language plpgsql; first create trigger enigmatrig after update on first for each row execute procedure enigma(); update first set a = a+3; select * from first; select * from second; a 1 2 second b 14

Triggers create or replace function what() returns trigger as $$ declare Solution: r nums%ROWTYPE; The first select will return begin 1, 1, 2, 3, 4 select max(a) into r from nums; The second select will return 1, 1, 2, 3, 4, 0 IF r. a<new. a THEN insert into nums values(new. a-1); nums END IF; a RETURN new; 1 end $$ language plpgsql; create trigger whattrig before insert on nums for each row execute procedure what(); insert into nums values(4); select * from nums; insert into nums values(0); select * from nums; 15

Triggers create or replace function whatr() returns trigger as $$ begin insert into S values(1); RETURN null; end $$ language plpgsql; create or replace function whats() returns trigger as $$ begin insert into T values(1); RETURN null; end $$ language plpgsql; R a S a T a 16

Triggers (cont) create trigger whatstrig before insert on S for each statement execute procedure whats(); create trigger whatrtrig before insert on R for each statement execute procedure whatr(); insert into R values(0); select * from R; select * from S; select * from T; insert into S values(0); select * from R; select * from S; select * from T; Solution: After first insert R: 0, S: 1, T: 1 After second insert R: 0 , S: 0, 1, T: 1, 1 R a S a T a 17