• Week 04 Monday
• PostgreSQL Stored Procedures
• Withdrawal Function in PLpgSQL
• SQL functions
• Exercise: SQL function
• PLpgSQL
• Example PLpgSQL function (i)
• Example PLpgSQL function (ii)
• PLpgSQL functions returning Sets
• Exercise: Function Overloading
• PLpgSQL function returning Sets of Tuples
• PLpgSQL functions using queries
• Exercise: The favBeers function
• Functions vs Views
• Exercise: Function vs View
• select into
• Exercise: select into

❖ Week 04 Monday

In today's lecture ...

• Stored Procedures
• PLpgSQL Procedural language

Before asking questions on the forum ...

• if you don't attend lectures, please watch the lecture videos
• want to work on stuff not yet covered in lectures?   watch Topic Videos

If asking questions via email (to cs3311@cse)

• you must include your name and zID

❖ Week 04 Monday (cont)

Things to do ...

• Quiz 3 due before Friday midnight (involves PLpgSQL)

Things to note ...

• Help Sessions (K17-G05): Mon 12-2, Tue 2-4, Thu 10-12, Fri 10-12
• This weekend is census weekend ... drop now ⇒ don't pay

❖ PostgreSQL Stored Procedures

PostgreSQL syntax for defining stored functions:

create or replace function
   funcName(arg1, arg2, ....) returns retType
as $$
String containing function definition
$$ language funcDefLanguage;

Notes:

• arg_i   consists of   name type
• $$ ... $$ are just another type of string quote  (c.f. '...')
• function definition languages: SQL, PLpgSQL, Python, ...

❖ Withdrawal Function in PLpgSQL

create or replace function
    withdraw(_acctNum text, _amount integer) returns text
as $$
declare bal integer;
begin
    select balance into bal
    from   Accounts
    where  acctNo = _acctNum);
    if (bal < _amount) then
        return 'Insufficient Funds';
    else
        update Accounts
        set    balance = balance - _amount
        where  acctNo = _acctNum;
        bal := bal - _amount;
        return 'New Balance: ' || bal;
    end if
end;

❖ SQL functions

The body of a PostgreSQL function can be an SQL query

create or replace function
   maxPriceAt(text) returns numeric(4,2)
as $$
select max(price)::numeric(4,2)
from   Sells where bar = $1;
$$ language sql;

which can be used as, e.g.

select * from maxPriceAt('Royal Hotel');
select maxPriceAt('Coogee Bay Hotel');
select name, maxPriceAt(name) from  Bars;

But still provides no procedural programming capability

❖ SQL functions (cont)

Value returned by an SQL function ...

• is the first value from the last  select  statement
• can be a tuple if function returns a tuple type

Parameters can be named

create or replace function
   maxPriceAt(_bar text) returns numeric(4,2)
as $$
select max(price)::numeric(4,2)
from   Sells where bar = _bar;
$$ language sql;

❖ Exercise: SQL function

Write an SQL function to find the most expensive beer that a drinker likes

create or replace function
   priciest(_drinker text) returns text
as $$
...
$$ language sql;

❖ PLpgSQL

PLpgSQL is PostgreSQL's stored procedure language

Allows you to write functions that combine

• SQL queries
• local data structures
• procedural programming   (sequence, selection, iteration)

PLpgSQL syntax is slightly old-fashioned   (keywords, not {...} )

Loosely based on Oracle's PL/SQL   (translating between PL/SQL and PLpgSQL is easy)

❖ Example PLpgSQL function (i)

An iterative factorial function in PLpgSQL

create or replace function
   ifac(n integer) returns integer
as $$
declare
   i   integer;       --uninitialised variable
   fac integer := 1;  -- initialised variable
begin
   for i in 2..n loop
      fac := fac * i;
   end loop;
   return fac;
end;
$$ language plpgsql;

❖ Example PLpgSQL function (ii)

A recursive factorial function in PLpgSQL

create or replace function
   rfac(n integer) returns integer
as $$
begin
   if (n < 2) then
      return 1;
   else
      return n * rfac(n-1);
   end if;
end;
$$ language plpgsql;

❖ PLpgSQL functions returning Sets

PLpgSQL functions can return sets of values, e.g.

create or replace function
   iota(_max integer) return setof integer
as $$ ... $$

If they return a set of tuples, they effectively return a table, e.g.

create or replace function
   favBeers(_drinker text) return setof Beers
as $$ ... $$

Each tuple/value is included in the result set by  return next Value

❖ Exercise: Function Overloading

A function is identified by its name and parameter types (its signature).

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()

❖ PLpgSQL function returning Sets of Tuples

As noted above, functions can return sets of tuples.

Return sets can be defined:

• on the fly, e.g.

  function F(...) returns table(x integer, y integer)
  

• defining a tuple type, e.g.

  create type Pair as (x integer, y integer);
  function F(...) returns setof Pair
  

• using the tuple type associated with a table, e.g

  create table Beers (id integer, name text, ...);
  function F(...) returns setof Beers
  

❖ PLpgSQL function returning Sets of Tuples (cont)

Individual tuples are added to result set via RETURN NEXT

How this is acheived differs between the methods.

• F(...) returns table(x integer, y integer)
  • x and y exist as implicit variables in the function
  • assign a value to each of these variables
  • return the tuple via  RETURN NEXT;
• F(...) returns set of Pair
  • need a variable p of type Pair in the function
  • assign values to p.x and p.y
  • return the tuple via  RETURN NEXT p;

❖ PLpgSQL functions using queries

PLpgSQL functions can invoke queries and iterate through their results

Example:

create function
    beersLike(partName text) returns setof Beers
as $$
declare
    tuple Beers;
begin
    for tuple in
        select * from Beers where name ~* partName
    loop
       return next tuple;
    end loop;
end;
$$ language plpgsql;

❖ PLpgSQL functions using queries (cont)

Deferred typing of query variables:

declare
   tuple record;
begin
   for tuple in Query
   loop
      Process tuple
   end loop;
   ...
end;

The tuple variable inherits the type of query tuples.

I.e. the type of a record variable is determined at query time.

❖ Exercise: The favBeers function

Implement a the favourite beers function

create or replace function
   favBeers(_drinker text) returns setof Beers
as $$
...
$$ language plpgsql;

Assume that every beer a drinker drinks is one of their favourites

❖ 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

❖ 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