• Week 04 Thursday
• More on PLpgSQL Functions
• Functions vs Views
• Exercise: Function vs View
• Exercise: Function Overloading
• select into
• Exercise: select into
• Missing Data
• A new database
• Exercise: Missing Data
• Exercise: Generating a Table
• An "old" database
• Exercise: A newBeer Function
• Debugging PLpgSQL
• Dynamic Queries
• Exercise: Dynamic Queries

❖ Week 04 Thursday

In today's lecture ...

• PLpgSQL
  • Assigning query results ... select  a,b,c  into  x,y,z   ...
  • Missing data ... if not found then ...
  • Debugging PLpgSQL ... raise notice  Message
  • Dynamic queries ... execute(StringContainingSQLQuery)
• Aggregates ... summarizing data

Things to do ...

• Census: Sunday is last day to withdraw without paying
• Quiz 3 due before Friday midnight (involves PLpgSQL)
• Set up your PostgreSQL server
  (only 420 students have a plausible PostgreSQL server on vxdb2)

❖ More on PLpgSQL Functions

Function definition ...

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

Type  can be

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

❖ Functions vs Views

Functions returning setof are used like tables.

Views are used like tables.

What's the difference?

• parameters can feed values into a function
• functions can manipulate data in "procedural ways"
• functions require more explicit "infrastructure"
• others?

❖ Exercise: Function vs View

Consider a view that gives Drinkers, but without their phone:

create or replace view d1(drinker,surburb)
as
...

Implement this view

Then implement the same thing as a function

Then add parameter to restrict to names matching pattern

❖ Exercise: Function Overloading

A function is udentified by its name and parameter types.

Can have several functions with same name, but different parameters.

Consider a function seq to return sequences of integer values

• seq(n) ... returns sequence 1 .. n
• seq(n,m) ... returns sequence n .. m
• seq(n,m,incr) ... returns sequence n .. m by incr

Implement this as three different PLpgSQL functions, all called  seq()

❖ select into

Can capture individual attributes in a select statement

select att1, att2, ... attn
  into var1, var2, ... varn
from   ...

Notes:

• atti  can be an arbitrary SQL expression
• must have same number/types of  atti  as  vari

Can also assign whole tuples, e.g.

create table R (...);

declare tup R;

select * into tup from R where Condition;

❖ Exercise: select into

Consider a table  R(a integer, b text, c float)

Implement different ways to collect individual attributes into variables

Variables are  x integer, y text, z float

❖ Missing Data

With 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 ...

❖ A new database

Examples based on Department/Employee database:

Departments(id, name, manager))
Employees(tfn,givenname,familyname,hourspweek,salary)
WorksFor(employee, department, percentage)
DeptMission(department,keyword)

Exercise: implement views and functions for

EmpDept(employee, department, fraction)

❖ 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

❖ Exercise: Generating a Table

Consider that we want a mechanism to give tuples

create type EmpDept as
(employee text, department text, fraction float)

using the data in the employees/departments database

Implement this as a view and as a function

❖ An "old" database

Recall the database for Assignment 1

Countries(id, code, name)

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

Styles(id, name, min_abv, max_abv)

Ingredients(id, itype, name, origin)

Beers(id, name, vintage, 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 $$ ...

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 ?

❖ 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

❖ 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

❖ 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.