Lecture 18: Procedural SQL and Database Program Complexity#
Announcements#
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.
Hw#6 to be posted very soon, due next week thursday
Expect one reasonable sized, or two smaller homeworks
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');