• Week 04 Tuesday
• Views (recap)
• Example Database: Beers/Bars/Drinkers
• Exercise: More Queries on Simple Beer DB
• More on SQL Patterns
• SQL as a Programming Language
• SQL as a Programming Language
• What's wrong with SQL?
• Database Programming
• PostgreSQL Stored Procedures
• SQL functions
• Exercise: SQL function
• PLpgSQL
• Exercise: A PLpgSQL function
• Exercise: Another PLpgSQL function
• PLpgSQL functions using queries
• PLpgSQL functions returning sets
• Exercise: The iota function
• Exercise: The favBeers function

❖ Week 04 Tuesday

In today's lecture ...

• more SQL Query Language
• Stored Procedures
• PLpgSQL Procedural language

Before asking questions on the forum ...

• if you don't attend lectures, please watch the lecture videos
• if you want to work on stuff next yet covered in lectures
  watch the relevant content videos (under Slides and Videos)

❖ Week 04 Tuesday (cont)

Things to do ...

• Quiz 3 due before Friday midnight (involves PLpgSQL)
• Set up your PostgreSQL server
  (520 students have logged in to vxdb2 and have /localstorage)
  

Will run a consultation session on Teams from 12-2 on Wednesday.

Reminder: this weekend is census weekend ... stay enrolled and pay

❖ Views (recap)

Views are defined as

create [or replace] view ViewName ( AttributeNames )
as
select ResultValues (one per attribute) from ... ;

Things to note:

• AttributeNames are outputs
• inherit their type from corresponding select expression
• each view has an associated tuple type (like a table does)

❖ Views (recap) (cont)

Example view definition:

create table R (x integer, y text, z char(3));
create view RR(a, b) as select x,y from R;

The view RR has type (integer,text)

You cannot redefine it as e.g.

create view RR(a, b) as select y,z from R;

This has type (text,char(3)), incompatible with the original type.

To change the number/types of view attributes:

drop view RR;
create view RR(a, b) as select y,z from R;

❖ Views (recap) (cont)

View definitions in ass1.sql template:

create or replace view Q1(beer, "sold in", alcohol)
as select null, null, null;

defines a view with type (text,text,text)

When you define your query, it must have the same attribute types.

Similarly

create or replace view Q2(beer, style, abv, reason)
as select null, null, null::ABVvalue, null;

has type (text,text,ABVvalue,text), and so must the query.

❖ Example Database: Beers/Bars/Drinkers

Consider the following ER model:

❖ Exercise: More Queries on Simple Beer DB

16. Which bar is most popular? (Most drinkers)
17. Which bar is most expensive?
    (Maximum average price)
18. Price of cheapest beer at each bar?
19. Name of cheapest beer at each bar?
20. Which beers are sold at all bars?
21. How many drinkers are in each suburb?
22. How many bars in suburbs where drinkers live?
    (Must include suburbs with no bars)

❖ More on SQL Patterns

Answering queries like: Which X's are RelatedTo all Y's

AllYs = select Y from Ys
for each X {
   YsRelatedToX = select Y from R where X
   if YsRelatedToX == AllYs
      X is added to the results
}

In SQL, you can't compare sets directly, so

not exists (
   (select Y from Ys)
   except
   (select Y from R where X)
)

❖ SQL as a Programming Language

SQL is a powerful language for manipulating relational data.

But it is not a powerful programming language.

At some point in developing complete database applications

• we need to implement user interactions
• we need to control sequences of database operations
• we need to manipulate query results in complex ways

and SQL cannot do any of these.

SQL cannot even do something as simple as factorial!

Ok ... so PostgreSQL added a factorial operator ... but it's non-standard.

❖ SQL as a Programming Language

SQL is a powerful language for manipulating relational data.

But it is not a powerful programming language.

At some point in developing complete database applications

• we need to implement user interactions
• we need to control sequences of database operations
• we need to manipulate query results in complex ways

and SQL cannot do any of these.

SQL cannot even do something as simple as factorial!

Ok ... so PostgreSQL added a factorial operator ... but it's non-standard.

❖ What's wrong with SQL?

Consider the problem of withdrawal from a bank account:

If a bank customer attempts to withdraw more funds than they have in their account, then indicate "Insufficient Funds", otherwise update the account

An attempt to implement this in SQL:

select 'Insufficient Funds'
from   Accounts
where  acctNo = AcctNum and balance < Amount;
update Accounts
set    balance = balance - Amount
where  acctNo = AcctNum and balance >= Amount;
select 'New balance: '||balance
from   Accounts
where  acctNo = AcctNum;

❖ What's wrong with SQL? (cont)

Two possible evaluation scenarios:

• displays "Insufficient Funds", UPDATE has no effect, displays unchanged balance
• UPDATE occurs as required, displays changed balance

Some problems:

• SQL doesn't allow parameterisation (e.g. AcctNum)
• always attempts UPDATE, even when it knows it's invalid
• need to evaluate (balance < Amount) test twice
• always displays balance, even when not changed

To accurately express the "business logic", we need facilities like conditional execution and parameter passing.

❖ Database Programming

Database programming requires a combination of

• manipulation of data in DB   (via SQL)
• conventional programming   (via procedural code)

This combination is realised in a number of ways:

• passing SQL commands via a "call-level" interface
  (prog lang is decoupled from DBMS; most flexible; e.g. Java/JDBC, JDBCython/psycopg2)
• embedding SQL into augmented programming languages
  (requires pre-processor for language; typically DBMS-specific; e.g. SQL/C)
• special-purpose programming languages in the DBMS
  (closely integrated with DBMS; enable extensibility; e.g. PL/SQL, PLpgSQL)

❖ Database Programming (cont)

Combining SQL and procedural code solves the "withdrawal" problem:

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

(This example is actually a stored procedure, using SQL/PSM syntax)

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

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

❖ Exercise: A PLpgSQL function

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;

❖ Exercise: Another PLpgSQL function

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 using queries

PLpgSQL functions can invoke and interate through queries

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

The tup variable inherits the type of query tuples.

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

❖ 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, the effectively return a table

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

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

❖ Exercise: The iota function

Implement a function called iota that

• takes an integer value n
• returns the set of numbers from 1.. n

create or replace function
   iota(_max integer) returns setof integer
as $$
...
$$ language plpgsql;

❖ Exercise: The favBeers function

Implement 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