• Week 04 Wednesday
• Assignment 1 Testing
• More on PLpgSQL Functions
• Missing Data
• Exercise: Missing Data
• A familiar database
• Exercise: A newBeer Function
• Dynamic Queries
• Exercise: Dynamic Queries
• Exceptions
• Exercise: Exceptions
• Iteration in PLpgSQL
• Exercise: Poor use of loop
• Returning Multiple Results
• Exercise: Withdrawal Function returning two values
• Developing PLpgSQL Functions
• Debugging PLpgSQL
• Aggregates
• Exercise: User-defined Aggregates

❖ Week 04 Wednesday

In today's lecture ...

• PLpgSQL
• Aggregates ... summarizing data

Things to do ...

• Census: Sunday is last day to withdraw without paying
• Quiz 3 due before Friday midnight (involves PLpgSQL)
• Assignment 1, due before midnight on Friday 13 October
  (25/650 submissions so far ... haven't checked their correctness)

❖ Assignment 1 Testing

This how we will test your submission:

createdb TempDB1
psql TempDB1 -f ass1.dump
psql TempDB1 -f ass1.sql (your code)
createdb TempDB2
psql TempDB2 -f unseen.dump
psql TempDB2 -f ass1.sql (your code)
... run tests on TempDB1 and TempDB2 ...
dropdb TempDB1
dropdb TempDB2

Make sure your code will work if loaded like this on  vxdb2

Of course, not including  unseen.dump  and  TempDB2

❖ More on PLpgSQL Functions

Function definition ...

create or replace function
   FunctionName ( Parameters ) returns [setof] Type
as $$
declare
   VariableDeclarations
begin
   FunctionBody
end;
$$ language plpgsql;

Type  can be

• atomic ... e.g.  integer,  real,  text,  char(8), ...
• tuple ... e.g. from  create table,  create view,  create type

❖ More on PLpgSQL Functions (cont)

So far ...

• Declaring local variables (including  records)
• Control structures ... if, for, while
• Returning results ... return, return next
• Assigning query results ... select  a,b,c  into  x,y,z   ...

Coming up ...

• Missing data ... if not found then ...
• Debugging PLpgSQL ... raise notice  Message
• Dynamic queries ... execute  StringContainingSQLQuery

❖ Missing Data

Within functions, often use parameter values to find tuples

What to do if value leads to no match ...

select * into tup from R where name ~ Value
if (not found) then
   handle case for no matching tuple
else
   handle case for matching tuple
end if

Alternative method (but requires two selects if there is a match)

select count(*) into nmatches from R where name ~ Value
if (nmatches = 0) then ...

❖ Exercise: Missing Data

Implement a PLpgSQL function

create or replace function
	tfnOf(name text) returns text
as $$ ...

That takes a name as argument ...

• returns their TFN if they exist
• returns "No such employee" if they don't

Assume:

Employees(tfn text primary key, name text, salary money, ...)

❖ A familiar database

Recall the database for Assignment 1

Locations(id, country, region, metro, town)

Styles(id, name, min_abv, max_abv)

Ingredients(id, itype, name)

Beers(id, name, brewed, style, abv, ibu, sold_in, ...)

Contains(beer, ingredient)

Brewed_by(beer, brewery)

❖ Exercise: A newBeer Function

Write a PLpgSQL function on the Ass1 database

create or replace function
   addBeer(_name text, _brewery text, _style text, _abv float)
   returns integer
as $$ ... $$ language plpgsql;

that takes the name, brewery, style and abv

• add a tuple to  Beers  for the new beer
• adds a tuple to  Brewed_by  to link beer and brewer
• raises a  Notice  if brewery or style don't exist
• returns the  id  of the new beer

What if the  Beers.id  field is defined as  serial ?

❖ 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

Example:

qry := 'select * from '||quote_ident('beers')||
       ' where name = '||quote_literal('Fairy Floss');
-- which becomes ...
qry := 'select * from beers where name = ''Fairy Floss''';

❖ Dynamic Queries (cont)

Query returning no results

perform 'update R set ...';

Query returning one result

execute 'Query' into Target;

Query returning multiple results

for Target in execute 'Query'
loop
   ... process Target ...
end loop;

❖ Exercise: Dynamic Queries

Write a PLpgSQL function

create or replace function
   nTuples(tableName text) return integer
as $$ ... $$ language plpgsql;

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

❖ Iteration in PLpgSQL

Use SQL queries within function do as much work as possible

Don't do using code what you could do using SQL

E.g. use where clause to avoid tests within loops

for T in select * from Table 
loop
   if T.x > 0 then
      process T ...
   end if;
end loop;

-- should be implemented as 

for T in select * from Table where x > 0
loop
   process T ...
end loop;

❖ Iteration in PLpgSQL (cont)

E.g. use join operation to avoid nested loops

for T in select * from T1
loop
   for S in select * from T2
   loop
      if T.x = S.y then ... 
   end loop;
end loop;

-- should be implemented as 

for Res in
   select * from R join S on x=y
loop
   process Res ...
end loop;

❖ 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

❖ Returning Multiple Results

Already known: returning a set of values  returns setof Type

What about a function that returns two different types of value?

E.g. withdrawal function returns new balance and a message

-- initial balance in account 777-432 is $2000

select withdrawal('777-432', 500, ...)
-- returns 'Withdrawal ok' and 1500

select withdrawal('777-432', 10000, ...)
-- returns 'Insufficient funds' and 1500

Could use OUT parameters (parameter assigned a value within function)

❖ Returning Multiple Results (cont)

Simple example of using OUT parameters:

create or replace function
    sum_n_product(x int, y int, OUT sum int, OUT prod int)
as $$
begin
    sum := x + y;
    prod := x * y;
end;
$$ language plpgsql;

db=# select sum_n_product(3,4)
 sum | prod
-----+------
  7  |  12
(1 row)

Effectively creates an anonymous tuple type  (sum,prod)

❖ Exercise: Withdrawal Function returning two values

Show how the

withdrawal(Acct text, Amount money) ...

function could be implemented

• using  OUT  parameters
• using an explicit tuple type

❖ 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

❖ Debugging PLpgSQL

PLpgSQL does not have I/O functions

How to do debugging (see intermediate states)?

Use PostgreSQL logging statement

raise notice 'Message' [ , Expr1 , Expr2 , ... ]

Examples:

declare x integer; y float;
...
raise notice 'x = %', x
raise notice 'x = %, y = %', x, y
raise notice 'x^2 = %', x*x

❖ 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 your own count aggregate

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

2. make a product aggregate

   select product(a) from R  →  6
   

3. simple string concatentation (comma-separated)

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

Assuming

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