========================================================================= 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 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 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 ; baking=> select epsum2(4) ; 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; 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 ; ------------------------- 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 ; ------------------- 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)