Lecture 18: Procedural SQL and Database Program Complexity#

Announcements#

  1. Exam #2 is being graded, hw#5 will be graded afterwards.

    • Some code just did not run at all, you need to be careful about simple bugs like missing semicolons.

  2. Hw#6 to be posted very soon, due next week thursday

    • Expect one reasonable sized, or two smaller homeworks

  3. No lecture exercise today

PL/PGSQL#

-- Running queries

CREATE OR REPLACE FUNCTION allstars() RETURNS varchar AS $$
DECLARE
   outstr VARCHAR ;
   myrow record ;
   myname VARCHAR ;
BEGIN
   outstr = '';
   FOR myrow IN
        SELECT baker 
        FROM results
        WHERE result = 'star baker'
        ORDER BY episodeid ASC
   LOOP
      SELECT fullname INTO myname FROM bakers WHERE baker = myrow.baker ;
      outstr = outstr || myname || E'\n';
   END LOOP ;
   RETURN outstr ;

END ;
$$ LANGUAGE plpgsql ;
-- returning a set of tuples

create table summary (
      baker varchar(10)
      , num  int
) ;      


CREATE OR REPLACE FUNCTION get_summary() RETURNS SETOF summary AS $$
DECLARE
    myrow summary%ROWTYPE ;
BEGIN
    FOR myrow in 
        select baker, count(*) as num
        from results
        where result = 'star baker'
	group by baker
        order by baker
    LOOP
        RETURN NEXT myrow ;
    END LOOP ;
    RETURN  ;
END ;
$$ LANGUAGE plpgsql ;
  • Example use:


select g.baker, count(*) as numt, g.num as starbakercnt
from get_summary() g, technicals t
where g.baker = t.baker and t.rank=1
group by g.baker, g.num ;

Complex problem - use of procedures to reduce query complexity#

Example: Degree K of an input actor X:#

  • Degree 0: actor themselves

  • Degree 1: all actors who played in a movie with X

  • Degree 2: all actors who played in a movie with a degree 1 actor (but are not in degree 0 or 1)

  • Degree k: all actors who played in a movie with a degree k-1 actor (but are not in degree 0 - (k-1) )

How can we do with queries?

  • Degree 0:

select id
from actors
where name like 'Patrick%'  and surname = 'Stewart';
  • Degree 1:

select count(distinct mr2.actorid)
from
   movieroles mr1
   , actors a
   , movieroles mr2
where
   a.name like 'Patrick%'
   and a.surname = 'Stewart'
   and mr1.actorid = a.id
   and mr2.movieid = mr1.movieid
   and mr2.actorid <> a.id;
  • Degree 2:

select count(distinct mr4.actorid)
from
   movieroles mr1
   , actors a
   , movieroles mr2
   , movieroles mr3
   , movieroles mr4
where
   a.name like 'Patrick%'
   and a.surname = 'Stewart'
   and mr1.actorid = a.id
   and mr2.movieid = mr1.movieid
   and mr2.actorid <> a.id
   and mr3.actorid = mr2.actorid --- movies for degree 1 people
   and mr4.movieid = mr3.movieid --- actors for mr3.movieid
   and mr4.actorid not in (  --- degree 1 people
         select distinct mr2.actorid
         from
           movieroles mr1
           , movieroles mr2
        where
          mr1.actorid = a.id
          and mr2.movieid = mr1.movieid) ;


 count  
--------
 101682

Now, let’s instead create a function for any k:

CREATE OR REPLACE FUNCTION degree_of(fname VARCHAR, lname VARCHAR, k int)
      RETURNS INT AS $$
DECLARE
   curdegree int ;
   outnum int ;
BEGIN
   DROP TABLE IF EXISTS tmpdegree ;
   CREATE TABLE tmpdegree (actorid int, degree int) ;

   INSERT INTO tmpdegree
   select id, 0 from actors
   where name like fname || '%'
   and surname = lname ;

   curdegree = 1 ;
   loop
      insert into tmpdegree
       select
           distinct mr2.actorid, curdegree
       from
           movieroles mr1, tmpdegree a, movieroles mr2
       where
          mr1.actorid = a.actorid
          and a.degree = curdegree-1
          and mr2.movieid = mr1.movieid
          and mr2.actorid not in (select actorid from tmpdegree);

       curdegree = curdegree + 1 ;
       IF curdegree > k THEN
           EXIT ;
       END IF ;	     
   end loop  ;

   SELECT count(*) INTO outnum from tmpdegree WHERE degree = k ;
   DROP TABLE IF EXISTS tmpdegree ;

   RETURN outnum ;
END ;
$$ LANGUAGE plpgsql ;

Object-relational Extensions of SQL#


CREATE TABLE cities (
     name            text
     , population      float
     , altitude        int     -- in feet
);

CREATE TABLE capitals (
     state           char(2)
) INHERITS (cities);


insert into cities values('New York City', 8175133, 33);
insert into cities values('Syracuse', 145170, 380);
insert into capitals values('Albany', 97856, 312, 'NY');