========================================================================= PLEASE DO NOT CREATE THE FUNCTIONS HERE OR ANY OBJECTS IN THE SHARED DATABASES IN THE CLASS SERVER. USE YOUR OWN DATABASE. LOOK AT THE LEFT DROPDOWN FOR DATABASES AND CHOOSE: db_ ========================================================================= PL/pgSQL: Documentation here: https://www.postgresql.org/docs/13/plpgsql.html C++/Python/plPGSQL -> procedural programming SQL -> declarative programming Basic structure: ------------------------------------- CREATE FUNCTION functionname(variable type) RETURNS type AS $$ DECLARE varname type ; BEGIN RETURN value ; END ; $$ LANGUAGE plpgsql ; ------------------------------------- For class exercise: Think about the "Degrees of Kevin Bacon" game. -> Kevin Bacon is: degree 0 (himself) -> Anyone who starred in a movie with Kevin Bacon is: degree 1 -> Anyone who starred in a movie with a degree 1 person is: degree 2 (and not degree 1 or 0) -> Anyone who starred in a movie with a degree n person is: degree n+1 (and not degree 0...n) .... Here is the table we can use (you can also use the moviecasts table from streaming db, but it is likely too small): imdb=> \d movieroles Table "public.movieroles" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- actorid | integer | | | movieid | integer | | | info_1 | text | | | info_2 | text | | | role | text | | | imdb=> select count(*) from movieroles ; count -------- 265107 (1 row) -- movies with KB. SELECT m.title FROM movies m , movieroles mr , actors a WHERE a.id = mr.actorid and m.id = mr.movieid and a.name = 'Kevin (I)' and a.surname = 'Bacon' ; -- actors who were in a movie with KB -- Degree 1 SELECT DISTINCT count(*) FROM actors a , movieroles mr --- movies with KB , movieroles mr1 -- actors in movies for mr1 , actors a1 WHERE a.id = mr.actorid and a.name = 'Kevin (I)' and a.surname = 'Bacon' and mr1.movieid = mr.movieid and mr1.actorid = a1.id and mr1.actorid <> a.id ; -- Degree 2 SELECT count(DISTINCT mr2.actorid) FROM actors a , movieroles mr --- movies with KB , movieroles mr1 -- actors in movies for mr1 , movieroles mr1m , movieroles mr2 WHERE a.id = mr.actorid and a.name = 'Kevin (I)' and a.surname = 'Bacon' and mr1.movieid = mr.movieid and mr1.actorid <> a.id and mr1m.actorid = mr1.actorid and mr2.movieid = mr1m.movieid and mr2.actorid <> a.id; -- Degree 2 but exclude degree 1 WITH degree1 AS ( SELECT DISTINCT mr1.actorid FROM actors a , movieroles mr --- movies with KB , movieroles mr1 -- actors in movies for mr1 WHERE a.id = mr.actorid and a.name = 'Kevin (I)' and a.surname = 'Bacon' and mr1.movieid = mr.movieid and mr1.actorid <> a.id ) SELECT count(DISTINCT mr2.actorid) FROM actors a , movieroles mr --- movies with KB , movieroles mr1 -- actors in movies for mr1 , movieroles mr1m , movieroles mr2 WHERE a.id = mr.actorid and a.name = 'Kevin (I)' and a.surname = 'Bacon' and mr1.movieid = mr.movieid and mr1.actorid <> a.id and mr1m.actorid = mr1.actorid and mr2.movieid = mr1m.movieid and mr2.actorid <> a.id and mr2.actorid not in (select actorid from degree1) ; CREATE OR REPLACE FUNCTION degrees(inputname varchar, inputsurname VARCHAR) RETURNS varchar AS $$ DECLARE result varchar ; curdegree int; counter int ; inputaid int ; myrow RECORD ; BEGIN SELECT a.id INTO inputaid FROM actors a WHERE a.name = inputname and a.surname = inputsurname ; CREATE TABLE degrees ( actorid INT , degree INT ) ; INSERT INTO degrees SELECT DISTINCT mr1.actorid , 1 FROM movieroles mr --- movies with KB , movieroles mr1 -- actors in movies for mr1 WHERE mr.actorid = inputaid and mr1.movieid = mr.movieid and mr1.actorid <> inputaid ; curdegree = 1 ; for counter in 1..4 loop --- find the next degree! INSERT INTO degrees SELECT DISTINCT mr1.actorid , curdegree+1 FROM movieroles mr --- movies with degree X people , movieroles mr1 -- actors in movies for mr1 , degrees d WHERE mr.actorid = d.actorid and d.degree = curdegree and mr1.movieid = mr.movieid and mr1.actorid <> inputaid and mr1.actorid not in (select actorid from degrees); curdegree = curdegree + 1; end loop ; result = ''; for myrow IN select degree, count(*) as num FROM degrees GROUP BY degree ORDER BY degree asc loop result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; end loop ; DROP TABLE IF EXISTS degrees ; RETURN result ; END ; $$ LANGUAGE plpgsql ; CREATE OR REPLACE FUNCTION degrees(inputname varchar, inputsurname VARCHAR, maxdegree INT) RETURNS varchar AS $$ DECLARE result varchar ; curdegree int; counter int ; inputaid int ; myrow RECORD ; BEGIN SELECT a.id INTO inputaid FROM actors a WHERE a.name = inputname and a.surname = inputsurname ; CREATE TABLE degrees (actorid INT, degree INT) ; INSERT INTO degrees values(inputaid, 0); curdegree = 0 ; for counter in 1..maxdegree loop --- find the next degree! INSERT INTO degrees SELECT DISTINCT mr1.actorid , curdegree+1 FROM movieroles mr --- movies with degree X people , movieroles mr1 -- actors in movies for mr1 , degrees d WHERE mr.actorid = d.actorid and d.degree = curdegree and mr1.movieid = mr.movieid and mr1.actorid not in (select actorid from degrees); curdegree = curdegree + 1; end loop ; result = ''; for myrow IN select degree, count(*) as num FROM degrees GROUP BY degree ORDER BY degree asc loop result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; end loop ; DROP TABLE IF EXISTS degrees ; RETURN result ; END ; $$ LANGUAGE plpgsql ; ------------------ CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ BEGIN RETURN NEW; END; $emp_stamp$ LANGUAGE plpgsql; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE FUNCTION emp_stamp(); BEFORE triggers AFTER triggers FOR EACH ROW/ FOR EACH STATEMENT Triggering event: INSERT/UPDATE/DELETE CREATE TABLE episodes_version AS (SELECT id, title, viewers7day FROM episodes); ALTER TABLE episodes_version ADD versionno INT ; UPDATE episodes_version SET versionno = 0 ; CREATE OR REPLACE FUNCTION baking_trigger_f() RETURNS trigger AS $$ DECLARE curversion INT ; BEGIN SELECT max(versionno) INTO curversion FROM episodes_version WHERE id = OLD.id ; INSERT INTO episodes_version VALUES(OLD.id, OLD.title, OLD.viewers7day, curversion+1) ; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER baking_trigger BEFORE UPDATE ON episodes FOR EACH ROW EXECUTE FUNCTION baking_trigger_f(); CREATE OR REPLACE FUNCTION baking_trigger_f() RETURNS trigger AS $$ BEGIN NEW.viewers7day = 8; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER baking_trigger BEFORE UPDATE ON episodes FOR EACH ROW EXECUTE FUNCTION baking_trigger_f(); -- after you insert into results, if it is for star baker, then go -- and update the number of wins for baker! alter table bakers add numwins int ; CREATE OR REPLACE FUNCTION baking_trigger_f() RETURNS trigger AS $$ BEGIN IF new.result = 'star baker' THEN UPDATE bakers SET numwins = (SELECT count(*) FROM results r WHERE r.baker = NEW.baker AND r.result = 'star baker') WHERE baker = NEW.baker ; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER baking_trigger AFTER INSERT OR UPDATE ON bakers FOR EACH ROW EXECUTE FUNCTION baking_trigger_f(); -------------------------------------- ----> PROCEDURE THAT IS UPDATED AFTER CLASS ENDED WITH OPTIMIZATIONS ----> THAT IMPROVED THE RUN TIME! CREATE OR REPLACE FUNCTION degrees(inputname varchar, inputsurname VARCHAR, maxdegree INT) RETURNS varchar AS $$ DECLARE result varchar ; curdegree int; counter int ; inputaid int ; myrow RECORD ; BEGIN SELECT a.id INTO inputaid FROM actors a WHERE a.name = inputname and a.surname = inputsurname ; CREATE TABLE degrees (actorid INT, degree INT) ; CREATE INDEX degrees_idx1 on degrees(degree,actorid); CREATE INDEX degrees_idx2 on degrees(actorid); INSERT INTO degrees values(inputaid, 0); curdegree = 0 ; for counter in 1..maxdegree loop --- find the next degree! INSERT INTO degrees SELECT DISTINCT mr1.actorid , curdegree+1 FROM movieroles mr --- movies with degree X people , movieroles mr1 -- actors in movies for mr1 , degrees d WHERE mr.actorid = d.actorid and d.degree = curdegree and mr1.movieid = mr.movieid EXCEPT SELECT actorid, curdegree+1 from degrees; curdegree = curdegree + 1; end loop ; result = ''; for myrow IN select degree, count(*) as num FROM degrees GROUP BY degree ORDER BY degree asc loop result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; end loop ; DROP TABLE IF EXISTS degrees ; RETURN result ; END ; $$ LANGUAGE plpgsql ;