Lecture 16 - Programming with PL/PGSQL

Announcements

- New Lecture exercise today (on Foreign keys from Thursday's lecture)

- Hw#5 to be released soon (may be tomorrow), due next week


PL/PGSQL

-- Basic functions, no queries

drop function return_desc(varchar, int, varchar) ;

CREATE OR REPLACE FUNCTION return_desc(a varchar, b int, c varchar)
       RETURNS varchar AS $$
DECLARE
    mydesc VARCHAR ;
BEGIN
    mydesc = a || ' (' || b::varchar || ') - ' || c;
    RETURN mydesc;
END ;
$$ LANGUAGE plpgsql ;

 select return_desc(fullname, age, occupation) from bakers ;


CREATE OR REPLACE FUNCTION format_name(t varchar, fname varchar, mname varchar, lname varchar)
	      RETURNS varchar AS $$
DECLARE
    myname VARCHAR ;
BEGIN
    IF mname is not null THEN
        myname = fname || ' ' || mname || ' '|| lname ;
    ELSE
        myname = fname || ' '|| lname ;
    END IF ;
    IF length(myname) < length(t) THEN
       RETURN myname ;
    ELSE
       RETURN t ;
    END IF ;
END ;
$$ LANGUAGE plpgsql ;


-- Functions that run a query that return a single row

CREATE OR REPLACE FUNCTION format_name(pid varchar)
	      RETURNS varchar AS $$
DECLARE
    myrec RECORD ;
    myname VARCHAR ;
BEGIN
    SELECT * INTO myrec FROM people WHERE personid = pid ;

    IF myrec.middlename is not null THEN
        myname = myrec.firstname || ' ' || myrec.middlename || ' '|| myrec.lastname ;
    ELSE
        myname = myrec.firstname || ' '|| myrec.lastname ;
    END IF ;
    IF length(myname) < length(myrec.title) THEN
       RETURN myname ;
    ELSE
       RETURN myrec.title;
    END IF ;
END ;
$$ LANGUAGE plpgsql ;

-- Function that run a query that return a single row
-- alternate way to read the returned results


CREATE OR REPLACE FUNCTION format_name(pid varchar)
	      RETURNS varchar AS $$
DECLARE
    t  varchar;
    fname varchar ;
    mname varchar ;
    lname VARCHAR ;
    myname VARCHAR ;
BEGIN
    SELECT title, firstname, middlename, lastname INTO t,fname,mname,lname
    FROM people WHERE personid = pid ;

    IF mname is not null THEN
        myname = fname || ' ' || mname || ' '|| lname ;
    ELSE
        myname = fname || ' '|| lname ;
    END IF ;
    IF length(myname) < length(t) THEN
       RETURN myname ;
    ELSE
       RETURN t ;
    END IF ;
END ;
$$ LANGUAGE plpgsql ;

   

-- Functions that return multiple tuples, you need to loop over

CREATE FUNCTION baker_eps(mybaker varchar) RETURNS VARCHAR AS $$
DECLARE
    myrec RECORD;
    eps VARCHAR ;
BEGIN
    eps = '';
    FOR myrec IN
       SELECT episodeid::varchar as ep FROM technicals
       WHERE baker = mybaker ORDER BY ep LOOP 
       eps = eps || myrec.ep || ' ' ;
    END LOOP ;
    return eps ;
END ;
$$ LANGUAGE plpgsql ;

CREATE OR REPLACE FUNCTION baker_eps(mybaker varchar) RETURNS VARCHAR AS $$
DECLARE
    epnum VARCHAR;
    eps VARCHAR ;
BEGIN
    eps = '';
    FOR epnum IN
       SELECT episodeid as ep FROM technicals
       WHERE baker = mybaker ORDER BY ep LOOP 
       eps = eps || epnum::varchar || ' ' ;
    END LOOP ;
    return eps ;
END ;
$$ LANGUAGE plpgsql ;

select fullname, baker_eps(baker) as eps from bakers order by eps;


-- Example runs:

-- Functions that return a set of tuples, i.e. a table


create table baker_eps_table (
    fullname varchar(255)
    , eps varchar(255)
) ;



create or replace function baker_eps() RETURNS SETOF baker_eps_table AS $$
DECLARE
    myrow RECORD ;
    outrow baker_eps_table%ROWTYPE ;
    eps VARCHAR ;
    curname VARCHAR ;
BEGIN
    eps = '';
    curname = '';
    FOR myrow IN
        SELECT b.fullname, t.episodeid
        FROM technicals t, bakers b
        WHERE t.baker = b.baker
        ORDER BY fullname asc, episodeid asc
    LOOP
        IF myrow.fullname <> curname THEN
	    IF curname <> '' THEN
  	      outrow.fullname = curname ;
	      outrow.eps = eps ;
	      RETURN NEXT outrow ;
	    END IF ;
	    eps = '' ;
	    curname = myrow.fullname;
        END IF ;	    
        eps = eps || myrow.episodeid::varchar || ' ' ;
    END LOOP ;
    outrow.fullname = curname ;
    outrow.eps = eps ;
    RETURN NEXT outrow ;
    RETURN ;
END ;

$$ LANGUAGE plpgsql ;


--- Example runs:

select baker_eps() ;
select * from  baker_eps() ;
select fullname from  baker_eps() ;
select eps from  baker_eps() ;
select * from  baker_eps() order length(eps);
select * from  baker_eps() order by length(eps);

----------

-- Complex operations should balance procedural and
-- query components, in complexity


IMDB database - Actors/Movies/MovieRoles

Important attributes:

actors(id,name,surname, ...)

movies(id, title, ...)

movieroles(actorid, movieid, ...)



Degrees of Actor X

- Degree 0 : Actor X

- Degree 1: All actors who starred in a movie with X

- Degree 2: All actors who starred in a movie with a degree 1 actor

- Degree X: All actors who starred in a movie with a degree X-1 actor


- Degree 0:

select id as degree0
from actors
where name = 'Liam' and surname = 'Neeson';

- Degree 1:

select distinct
    mr2.actorid as degree1
from
    actors a
    , movieroles mr
    , movieroles mr2
where
    a.name = 'Liam'
    and a.surname = 'Neeson'
    and a.id = mr.actorid
    and mr2.movieid = mr.movieid
    and mr2.actorid <> a.id ;


- Degree 2:

-- mr2.actorid is degree1

select distinct
    mr4.actorid  as degree2
from
    actors a
    , movieroles mr
    , movieroles mr2
    , movieroles mr3
    , movieroles mr4
where
    a.name = 'Liam'
    and a.surname = 'Neeson'
    and a.id = mr.actorid
    and mr2.movieid = mr.movieid
    and mr2.actorid <> a.id
    and mr2.actorid = mr3.actorid
    and mr4.movieid = mr3.movieid
    and mr4.actorid not in (
           select mr2_i.actorid
           from
             movieroles mr_i
             , movieroles mr2_i
           where
              a.id = mr_i.actorid
              and mr2_i.movieid = mr_i.movieid
              and mr2_i.actorid <> a.id )
          ;


---------------------
-- Pseudo code for a simpler solution to any degree!

create table degrees(actorid int, degree int) ;

insert into degrees
   select id, 0
   from actors
   where name = 'Liam' and surname = 'Neeson';

curdegree = 0 ;
loop while not finished

   insert into degrees
   select distinct
      mr2.actorid, curdegree+1
   from
      degrees d
      , movieroles mr
      , movieroles mr2
   where
      mr.actorid = d.actorid
      and d.degree = curdegree
      and mr2.movieid = mr.movieid
      and mr2.actorid not in (select actorid from degrees) ;

   curdegree = curdegree + 1;

end loop ;