PL/pgSQL: Documentation here: https://www.postgresql.org/docs/13/plpgsql.html Disclaimer: not all functions I write here are things that are good ideas! Basic structure: ------------------------------------- CREATE FUNCTION functionname(variable type) RETURNS type AS $$ DECLARE varname type ; BEGIN RETURN value ; END ; $$ LANGUAGE plpgsql ; -------------------------------------- Data types: user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD; drop function epsum(int, date, varchar) ; CREATE OR REPLACE FUNCTION epsum(id int, epdate date, name varchar) RETURNS varchar AS $$ DECLARE sumdata varchar ; BEGIN sumdata = 'Episode: ' || id::varchar || ' titled "' || name || '" took place on ' || cast(epdate as varchar) ; RETURN sumdata ; END ; $$ LANGUAGE plpgsql ; SELECT epsum(1, date('2018-01-20'), 'abc'); SELECT title, epsum(id, firstaired, title) from episodes ; ---------------- Queries that return a single tuple: DROP FUNCTION epsum2 (int); CREATE OR REPLACE FUNCTION epsum2(inputepid int) RETURNS varchar AS $$ DECLARE sumdata varchar ; epdate date ; eptitle varchar ; BEGIN SELECT firstaired, title INTO epdate, eptitle FROM episodes where id = inputepid ; sumdata = 'Episode: ' || inputepid::varchar || ' titled "' || eptitle || '" took place on ' || cast(epdate as varchar) ; RETURN sumdata ; END ; $$ LANGUAGE plpgsql ; select epsum2(4) ; select epsum2(id) from episodes ; DROP FUNCTION epsum3 (int); CREATE OR REPLACE FUNCTION epsum3(inputepid int) RETURNS varchar AS $$ DECLARE sumdata varchar ; myrow RECORD ; BEGIN SELECT firstaired, title INTO myrow FROM episodes where id = inputepid ; sumdata = 'Episode: ' || inputepid::varchar || ' titled "' || myrow.title || '" took place on ' || cast(myrow.firstaired as varchar) ; RETURN sumdata ; END ; $$ LANGUAGE plpgsql ; ---------------- Queries that return more than one tuple: DROP FUNCTION bakerinfo(varchar); CREATE OR REPLACE FUNCTION bakerinfo(inputbaker varchar) RETURNS varchar AS $$ DECLARE myrow RECORD; summary VARCHAR ; BEGIN summary = 'Baker ' || inputbaker || ' was star baker in: '; FOR myrow IN SELECT episodeid FROM results WHERE result = 'star baker' AND lower(baker) = lower(inputbaker) LOOP summary = summary || myrow.episodeid::varchar || ' '; END LOOP; RETURN summary || '.'; END ; $$ LANGUAGE plpgsql ; select bakerinfo('Rahul'); DROP FUNCTION bakerinfo(varchar); CREATE OR REPLACE FUNCTION bakerinfo(inputbaker varchar) RETURNS varchar AS $$ DECLARE myrow RECORD; summary VARCHAR ; BEGIN summary = 'Baker ' || inputbaker || ' was star baker in: '; FOR myrow IN SELECT episodeid FROM results WHERE result = 'star baker' AND lower(baker) = lower(inputbaker) LOOP summary = summary || myrow.episodeid::varchar || ' '; END LOOP; summary = trim(summary) || ' and favorite in: ' ; FOR myrow IN SELECT episodeid FROM favorites WHERE lower(baker) = lower(inputbaker) LOOP summary = summary || myrow.episodeid::varchar || ' '; END LOOP; summary = trim(summary) || '.' || E'\n' || 'Won technical in: ' ; FOR myrow IN SELECT episodeid FROM technicals WHERE rank = 1 AND lower(baker) = lower(inputbaker) LOOP summary = summary || myrow.episodeid::varchar || ' '; END LOOP; summary = trim(summary) || '.' || E'\n' || 'Eliminated in: ' ; FOR myrow IN SELECT episodeid FROM results WHERE result = 'eliminated' AND lower(baker) = lower(inputbaker) LOOP summary = summary || myrow.episodeid::varchar ; END LOOP; RETURN summary || '.'; END ; $$ LANGUAGE plpgsql ; select baker, bakerinfo(baker) from bakers; select bakerinfo('Dan'); ---------------- Queries that are run many times: cursors DROP FUNCTION youngerbakers(); CREATE OR REPLACE FUNCTION youngerbakers() RETURNS VARCHAR AS $$ DECLARE bakerval VARCHAR; curs1 CURSOR FOR SELECT b.fullname FROM bakers b, bakers b2 WHERE b.age < b2.age AND b2.baker = bakerval ORDER BY b.fullname ASC; myrow RECORD ; myrow2 RECORD ; summary VARCHAR ; BEGIN summary = '' ; FOR myrow IN SELECT baker, fullname FROM bakers LOOP summary = summary || 'baker: ' || myrow.fullname ; summary = summary || E'\n' || 'Younger bakers: ' ; bakerval = myrow.baker; FOR myrow2 IN curs1 LOOP summary = summary || myrow2.fullname || E' '; END LOOP ; summary = summary || E'\n' ; END LOOP ; RETURN summary ; END ; $$ LANGUAGE plpgsql ; select youngerbakers() ; ------------------------- Queries that return more than one tuple, i.e. a relation! DROP TABLE IF EXISTS younger; CREATE TABLE younger (baker1 varchar(10), baker2 varchar(10)); CREATE OR REPLACE FUNCTION youngerbakers2() RETURNS SETOF younger AS $$ DECLARE bakerval VARCHAR; myrow RECORD ; returnrow younger%rowtype; BEGIN FOR myrow IN SELECT b1.baker as baker1, b2.baker as baker2 FROM bakers b1, bakers b2 WHERE b1.age < b2.age LOOP returnrow.baker1 = myrow.baker1; returnrow.baker2 = myrow.baker2; RETURN NEXT returnrow; END LOOP ; RETURN ; END ; $$ LANGUAGE plpgsql ; select b1.*,b.fullname from youngerbakers2() as b1, bakers b where b1.baker1=b.baker ; select * from youngerbakers2() ; select youngerbakers2() ; --------------------- Procedures (that do not return anything, a newer addition to pl/pgsql): CREATE OR REPLACE PROCEDURE test1() AS $$ BEGIN CREATE TABLE summary AS SELECT baker, count(*) as count FROM technicals WHERE rank=1 GROUP BY baker ; COMMIT; END; $$ LANGUAGE plpgsql ; CALL test1() ; ------------------- Triggers: ------------- Documentation here: https://www.postgresql.org/docs/13/triggers.html Database level constraints: ----------------------------- - People in the favorites relation should not be star bakers - Add numwins attribute to bakers: numwins should be equal to the number of star baker wins - Add versioning: table ep_history should contain past data from episodes table Assertions vs. Triggers: Assertion: what must be true in database (SQL standard) Triggers: programming approach, how to fix issues an update/delete/insert may cause Triggering events: update/delete/insert Under certain conditions: when statement What to do: a function/procedure Row level (for each row) or statement level If not careful: - Infinite loops - Bad data Any trigger that is activated becomes part of the transaction! Basic structure: ------------------------------------- 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(); -------------------------------------- NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations. -- Fix baker names before they are inserted insert into bakers values (' konstantin'); delete from bakers where baker = ' konstantin'; CREATE FUNCTION fix_baker () RETURNS trigger AS $$ BEGIN NEW.baker = initcap(trim(NEW.baker)); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER fix_baker BEFORE INSERT OR UPDATE ON bakers FOR EACH ROW EXECUTE FUNCTION fix_baker(); -- People in the favorites relation should not be star bakers -- If inserting someone as a star baker, remove them from favorites CREATE FUNCTION fix_favorites () RETURNS trigger AS $$ BEGIN IF NEW.result = 'star baker' THEN DELETE FROM favorites WHERE baker = NEW.baker AND episodeid = NEW.episodeid ; END IF ; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER fix_favorites AFTER INSERT OR UPDATE ON results FOR EACH ROW EXECUTE FUNCTION fix_favorites(); SELECT * FROM favorites ; SELECT * FROM results ; INSERT INTO results VALUES(1, 'Briony', 'star baker') ; 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 SELECT DISTINCT a1.name, a1.surname 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 1 SELECT count(DISTINCT mr1.actorid) 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 ; select degrees('Tom', 'Cruise'); 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 ; select degrees('Tom', 'Cruise', 2); 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 ; ------------------ 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();