• Week 05 Tuesday
• Submitting/Marking Assignment 1
• Assignment Hints
• Developing Complex SQL Queries
• Developing PLpgSQL Functions
• Iteration in PLpgSQL
• Exercise: Poor use of loop
• Dynamic Queries
• Exercise: Dynamic Queries
• Exceptions
• Exercise: Exceptions
• Aggregates
• Exercise: User-defined Aggregates
• Global Constraints
• Triggers
• Triggers in PostgreSQL
• Exercise: Trigger Example (i)
• Exercise: Trigger Example (ii)
• Programming with Databases

❖ Week 05 Tuesday

In this week's lectures ...

• PLpgSQL, Aggregates, Triggers, DBMS↔PL

Things to do ...

• Assignment 1 due before Friday 11:59pm
  (60 submissions so far; 500 more to go)
• Set up your PostgreSQL server
  (550 students have /localstorage, X have PostgreSQL servers)
• Check your assignment on  vxdb2  before submitting

❖ Submitting/Marking Assignment 1

Test on  vxdb2 before submitting

Submit via:  give cs3311 ass1 ass1.sql

Will be tested via:

dropdb ass1;  createdb ass1;  psql ass1 -f ass1.dump
psql ass1 -f ass1.sql # your submission
3311 autotest ass1
dropdb ass1;  createdb ass1;  psql ass1 -f ass1a.dump
psql ass1 -f ass1.sql # your submission
3311 autotest ass1

❖ Submitting/Marking Assignment 1 (cont)

Tutors will look at:  ass1.sql, output from two test runs

Up to 1 mark for style; ugly code gets 0

Consistency of style is important;   use SQL from lectures as guide

If view fails auto-test

• can still get some marks for that question
• depending on how close it is to correct answer
• no marks if the style is bad and too hard to read

❖ Assignment Hints

Some ideas ...

• hints:  case..when,    regexp_replace(),    string_agg()
• look up things in PostgreSQL documentation for more detail
• write helper views that are useful in several questions
• run extra queries to verify parts of your query or helper views
• use  raise notice  for debugging PLpgSQL functions

"Unseen" database for auto-marking has same schema, different data

• aims to check whether your solutions work in the general case

❖ Assignment Hints (cont)

If you get this error ...

nw-syd-vxdb2 % psql ass1 -f ass1.sql
psql: error: ass1.sql: No such file or directory

you're in the wrong directory.

Try cd until ls shows ass1.sql

If you get this error ...

ass1=#
ERROR: already defined V

change create view to create or replace view

❖ Developing Complex SQL Queries

Work backwards from the final query

• if I had a table like R, the final query would be easy
  (e.g. could use a pattern like  select x from R where y = (select min(y) from R))
• write a view to produce R
• if I had table S, producing R would be easy
• write a view to produce S
• etc. etc.

Try to minmise the complexity of individual views

Use attribute names in the  create or replace view  statement
(easier to keep track of what results the view is producing for use in other views)

Cannot change number/types of view attributes without dropping view

❖ Developing PLpgSQL Functions

Determine function signature

• what args are passed in, what result(s) returned

For function producing a single result

• develop a query to extract the data you need
• combine the data to produce result value/tuple

For function producing a set of results

• develop a query to extract useful data
• iterate through tuples   (for Rec in Query loop ...)
• incrementally build the result set

❖ Iteration in PLpgSQL

Use SQL queries within function do as much work as possible

Don't do using code what you could do using

• where clause ... avoids tests within loops

  for each tuple T in Table {        replace Table by
     if Cond(T) then ...             select * from Table
  }                                  where Cond
  

• join operation ... avoids nested loops

  for each tuple T in T1 {            replace by
     for each tuple S in T2 {         select *
        if T.x = S.y then ...         from T1 join T2 on x=y
  }  }
  

❖ Iteration in PLpgSQL (cont)

Example: function returning   setof Beers

create or replace function bb(_patt text) returns setof Beers
as $$
declare
   b Beers;
begin
   for b in select * from Beers
   loop
      if b.name ~ _name then
         return next b;
      end if
   end loop;
end;
$$ language plpgsql;

vs

create or replace function bb(_patt text) returns setof Beers
as $$
declare
   b Beers;
begin
   for b in select * from Beers where name ~ _name
   loop
      return next b;
   end loop;
end;
$$ language plpgsql;

❖ Exercise: Poor use of loop

Which of the following is the preferred way of filtering

   for rec in
      select * from R
   loop
      if (rec.a = 5) then
         ...
      end if;
   end loop;
OR
   for rec in
      select * from R where a = 5
   loop
      ...
   end loop;

Why?    Assume:  |R| = 1000 tuples,  10 tuples with  a=5

❖ Dynamic Queries

Queries can be "built" as strings of SQL code within functions

And then evaluated using  execute  Query

Including constant and identifiers in queries needs

• quote_literal ... ensure that constants are appropriately quoted
• quote_ident ... ensure that identifiers are quoted, if needed

Use  execute Query into Var   to capture result

Can also use in  for Rec in execute Query loop ...

❖ Exercise: Dynamic Queries

Write a PLpgSQL function

create or replce function
   nTuples(tableName text) return integer
as $$ ...

The function

• takes the name of a table as an argument
• returns the number of tuples in that table

See the  dbPop()  function for an extension of this idea.

❖ Exceptions

Exceptional conditions can arise during function execution

PLpgSQL provides mechanisms

• to catch exceptional conditions
• to handle caught exceptions

Syntax:

begin
   ... some code ...
exception
   when exceptionName then
      ... code to handle exception ...
end;

❖ Exercise: Exceptions

Implement a "safe" division function

create or replace function
   div(n integer, m integer) returns integer

• returns n/m if m is non-zero
• returns null if m is zero

❖ Aggregates

Aggregates reduce a collection of values to a single value

Example:

select avg(mark) from Enrolments where course='COMP3311'

How to achieve this? ... Maintain state, update value-by-value

State = initial state
for each tuple T in query {
    # update State to include T
    State = updateState(State, T)
}
return makeFinal(State)

❖ Aggregates (cont)

New aggregates are defined using CREATE AGGREGATE statement:

CREATE AGGREGATE AggName(BaseType) (
    stype     = StateType,
    initcond  = InitialValue,
    sfunc     = UpdateStateFunction,
    finalfunc = MakeFinalFunction
);

• initcond (type StateType) is optional; defaults to NULL
• finalfunc is optional; defaults to identity function

❖ Exercise: User-defined Aggregates

1. make a product aggregate

   select product(a) from R  →  6
   

2. simple string concatentation (comma-separated)

   select cat(b) from R  →  'this,is,fun'
   

3. make your own count aggregate

   select mycount(a) from R  →  3
   select mycount(b) from R  →  3
   

Assuming

create table R (a integer, b text);
insert into R values (1,'this'), (2,'is'), (3,'fun');

❖ Global Constraints

Column and table constraints ensure validity of one table

Ref. integrity constraints ensure connections between tables are valid

Global constraints may involve conditions over many tables

Simple example (from banking domain):

-- accounts are held at branches
-- assets of branch is sum of balances of its accounts

for all Branches b
   b.assets == (select sum(acct.balance)
                from   Accounts acct
                where  acct.branch = b.location)

❖ Global Constraints (cont)

SQL implementation of global constraints is ASSERTION

Example: #students in any UNSW course must be < 10000

create assertion ClassSizeConstraint check (
   not exists (
      select c.id
      from   Courses c
             join Enrolments e on (c.id = e.course)
      group  by c.id
      having count(e.student) > 9999
   )
);

Must be checked after every change to either Courses or Enrolments

Too expensive, so DBMSs provide triggers to do targetted checking.

❖ Triggers

Triggers are

• procedures stored in the database
• activated in response to database events   (e.g. insert/update/delete)

Examples of uses for triggers:

• maintaining summary data
• checking schema-level constraints (assertions) on update
• performing multi-table updates (to maintain assertions)

Triggers provide event-condition-action programming

❖ Triggers (cont)

Sequence of activities during database update:

Reminder: BEFORE and UPDATE triggers can modify value of new tuple

❖ Triggers in PostgreSQL

PostgreSQL triggers provide a mechanism for

• INSERT, DELETE or UPDATE events
• to automatically activate PLpgSQL functions

Syntax for PostgreSQL trigger definition:

CREATE TRIGGER TriggerName
{AFTER|BEFORE}  Event1 [OR Event2 ...]
ON TableName
[ WHEN ( Condition ) ]
FOR EACH {ROW|STATEMENT}
EXECUTE PROCEDURE FunctionName(args...);

• BEFORE triggers must return old or new
• any exception during a trigger forces roll-back

❖ Exercise: Trigger Example (i)

Consider two tables

create table R (id integer, val text);
create table S (r integer references R(id), value text);

Write a trigger to check that the foreign key value is valid

• when you insert a new S tuple
• when you update an existing S tuple

❖ Exercise: Trigger Example (ii)

Class enrolments:

Classes(id,name,room,day,start,end,quota,nstu)
ClassEnrolments(student,class)

Define triggers to maintain nstu for

insert into ClassEnrolments values (5012345, 6732);

insert into ClassEnrolments values (9999999, 99999);

delete from ClassEnrolments
where  student = 5012345 and class = 6732;

❖ Programming with Databases

So far, we have seen ...

• accessing data via SQL queries
• packaging SQL queries as views/functions
• building functions to return tables
• implementing assertions via triggers

All of the above programming

• is very close to the data
• takes place inside the DBMS

For applications need a PL interacting with the DBMS.

❖ Programming with Databases (cont)

Programming Language / DBMS archtecture:

❖ Programming with Databases (cont)

Consider this (imaginary) PL/DBMS access method:

--  establish connection to DBMS
db = dbAccess("DB");
query = "select a,b from R,S where ... ";
--  invoke query and get handle to result set
results = dbQuery(db, query);
--  for each tuple in result set
while (tuple = dbNext(results)) {
    --  process next tuple
    process(tuple['a'], tuple['b']);
}

Estimated costs:  dbAccess = 500ms,  dbQuery = 200ms,  dbNext < 1ms

❖ Programming with Databases (cont)

Example: find mature-age students   (e.g. 10000 students, 100 over 40)

query = "select * from Student";
results = dbQuery(db, query);
while (tuple = dbNext(results)) {
    if (tuple['age'] >= 40) {
        --  process mature-age student
    }
}

We transfer 10000 tuples from DB, 9500 are irrelevant

Cost = 1 query + 10000 tuple fetches

❖ Programming with Databases (cont)

Should be implemented as:

query = "select * from Student where age >= 40";
results = dbQuery(db, query);
while (tuple = dbNext(results)) {
    --  process mature-age student
}

Transfers only the 100 tuples that are needed.

Cost = 1 query + 100 tuple fetches

❖ Programming with Databases (cont)

Example: find info about all marks for all students

query1 = "select id,name from Student order by id";
res1 = dbQuery(db, query1);
while (tuple1 = dbNext(res1)) {
    query2 = "select course,mark from Marks"
             + " where student = " + tuple1['id'];
    res2 = dbQuery(db,query2);
    while (tuple2 = dbNext(res2)) {
        --  process student/course/mark info
    }
}

E.g. 10000 students, each with 8 marks, ⇒ run 10001 queries

Cost = 10001 queries + 80000 tuple fetches

❖ Programming with Databases (cont)

Should be implemented as:

query = "select id,name,course,mark"
        + " from Student s join Marks m "
        + " on (s.id=m.student)"
        + " order by s.id"
results = dbQuery(db, query);
while (tuple = dbNext(results)) {
    --  process student/course/mark info
}

We invoke 1 query, and transfer same number of tuples.

Cost = 1 query + 80000 tuple fetches