COMP3311 24T1
Database Systems
week 4 - 2
Outline
● Announcements
● Roadmap & Recap
● PLpgSQL
Announcement 1
● Assignment - 1
○ specifications are released (without the PL/pgsql questions; they will be released
by tomorrow 8th Mar)
○ submission before 10:00 pm on 22nd Mar (Friday of week 6)
○ SQL views + SQL functions + PL/pgsql functions
● Tips:
○ get the (free) styling mark: https://sqlformat.org/
⚠
Announcement 2
● Help Sessions
○ Timetable: https://webcms3.cse.unsw.edu.au/COMP3311/24T1/resources/96822
○ Tue 2:00PM - 4:00PM
○ Wed 2:00PM - 4:00PM
○ Thu 3:00PM - 5:00PM
Announcement 3
● Quiz 3
○ question 3 & 4 (pl/pgsql)
○ hints: try to execute and observe
○ JUST DO IT!
Outline
● Announcements
● Roadmap & Recap
● PLpgSQL
Roadmap & Recap
In our last lecture, we have covered:
Bags/Sets and Their Operators
● Concept: difference? result of queries? how to convert to sets?
● Set Operators: 3 types, convert result to bags?
K
● IN/EXISTS: difference?
● ANY/ALL: difference?
Roadmap & Recap
In our last lecture, we have covered:
Grouping
● Concept: filtering?
● Partition By: concept? vs group?
K
Roadmap & Recap
In our last lecture, we have covered:
Abstraction
● Alternatives: 3?
● Recursive Query: scenario? keyword?
K
Roadmap & Recap
In our last lecture, we have covered:
SQL Stored Function & Procedure
● Stored Function: Syntax? Return type? 2 choices. Accessing parameters? 2 choices.
Invoke?
● Stored Procedure: Syntax? Return? Invoke? vs Function?
K
Roadmap & Recap
Our plan:
Learn Pl/pgsql Today
Have a BIG recap session on next Monday; Continue the study on next Thursday
Outline
● Announcements
● Roadmap & Recap
● PLpgSQL
PL/pgSQL
PLpgSQL = Procedural Language extensions to PostgreSQL
K
A PostgreSQL-specific language integrating features of:
● procedural programming and SQL programming
Provides a means for extending DBMS functionality, e.g.
● implementing constraint checking
● complex query evaluation (e.g. recursive)
● complex computation of column values
● detailed control of displayed results
PostgreSQL: Documentation: 16: Chapter 43. PL/pgSQL — SQL Procedural Language
Defining PLpgSQL Functions
PLpgSQL functions are created (and inserted into db) via:
K
CREATE OR REPLACE FUNCTION
funcName(arg1name arg1type, arg2name
arg2type, ....)
RETURNS rettype
AS $$
DECLARE
variable declarations
BEGIN
code for function
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION
funcName(arg1name arg1type,
arg2name arg2type, ....)
RETURNS rettype
AS $$
SQL statements
$$ LANGUAGE sql;
PLpgSQL Function Example
Example: function to compute x / y "safely"
create or replace function
div(x integer, y integer) returns integer
as $$
declare
result integer; -- variable
begin
if (y <> 0) then -- conditional
result := x/y; -- assignment
else
result := 0; -- assignment
end if;
return result;
end;
$$ language plpgsql;
PLpgSQL Function Example
Example: function to compute n!
create or replace function
factorial(n integer) returns integer
as $$
declare
i integer;
fac integer := 1;
begin
for i in 1..n loop
fac := fac * i;
end loop;
return fac;
end;
$$ language plpgsql;
Example: function to compute n! recursively
create function
factorial(n integer) returns integer
as $$
begin
if n < 2 then
return 1;
else
return n * factorial(n-1);
end if;
end;
$$ language plpgsql;
Usage: select factorial(5);
PLpgSQL Function Example
Example: handle withdrawal from account and return status message (SQL + procedural)
create 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;
select balance into bal
from Accounts
where acctNo = acctNum;
return 'New Balance: ' || bal;
end if;
end;
$$ language plpgsql;
Data Types
PLpgSQL constants and variables can be defined using:
● standard SQL data types (CHAR, DATE, NUMBER, ...)
● user-defined PostgreSQL data types (e.g. Point)
● a special structured record type (RECORD)
● table-row types (e.g. Branches%ROWTYPE or simply Branches)
● types of existing variables (e.g. Branches.location%TYPE)
There is also a CURSOR type for interacting with SQL.
K
Data Types (Record)
Record Variables:
● A record variable is similar to a row-type variable.
● It can hold only one row of a result set.
K
● Unlike a row-type variable, a record variable does not have a predefined structure.
● The structure of a record variable is determined when a select or for statement
assigns an actual row to it.
To declare a record variable, you use a variable name followed by the record keyword, like this:
variable_name record;
To access a field in the record, use the dot notation (.) syntax:
record_variable.field_name;
⚠ If you attempt to access a field in a record variable before it is assigned, you will get an error.
It’s important to note that a record is not a true data type but a placeholder.
Syntax/Control Structures
Typical set of control structures, with extensions:
K
Assignment var := expr
SELECT expr INTO var
Selection IF Cond1 THEN S1
ELSIF Cond2 THEN S2 ...
ELSE S END IF
Iteration LOOP S END LOOP
WHILE Cond LOOP S END LOOP
FOR rec_var IN Query LOOP …
FOR int_var IN lo..hi LOOP ...
Si = list of PLpgSQL
statements, each
terminated by
semi-colon
SELECT...INTO
Can capture query results via:
K
SELECT Exp1,Exp2,...,Expn
INTO Var1,Var2,...,Varn
FROM TableList
WHERE Condition ...
The semantics:
● execute the query as usual
● return "projection list" (Exp1,Exp2,...) as usual
● assign each Expi to corresponding Vari
SELECT...INTO- example
CREATE TABLE Products (
ProductID SERIAL PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10,2),
Quantity INT
);
INSERT INTO Products (ProductName, Price, Quantity) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 15),
('Tablet', 600.00, 20),
('Monitor', 300.00, 12),
('Keyboard', 50.00, 30);
CREATE OR REPLACE FUNCTION GetProductDetails(product_id INT)
RETURNS VOID AS $$
DECLARE
vProductName VARCHAR(100);
vPrice DECIMAL(10,2);
vQuantity INT;
productDetail RECORD;
rProductDetail Products%ROWTYPE;
BEGIN
-- SELECT ProductName, Price, Quantity INTO vProductName, vPrice, vQuantity
-- FROM Products
-- WHERE ProductID = product_id;

-- 1 what will happen if we remove the where clause in the previous select?
-- SELECT ProductName, Price, Quantity INTO vProductName, vPrice, vQuantity
-- FROM Products;

-- For demonstration, we'll just output the fetched details
-- RAISE NOTICE 'Product Name: %, Price: $%, Quantity: %', vProductName, vPrice, vQuantity;


-- 2 Let's try to play with the record type
-- SELECT ProductName, Price, Quantity INTO productDetail
-- FROM Products
-- WHERE ProductID = product_id;

-- 4 what will happen if we remove the where clause in the previous select?
-- SELECT ProductName, Price, Quantity INTO productDetail
-- FROM Products;

-- 3 what will happen if we just print the productDetail without select into?
-- RAISE NOTICE 'Product Detail: %', productDetail;

-- 5 Let's try to play with the row type
-- SELECT * INTO rProductDetail
-- FROM Products
-- WHERE ProductID = product_id;

-- 7 what will happen if we remove the where clause in the previous select?
-- SELECT * INTO rProductDetail
-- FROM Products;

-- 6 what will happen if we just print the productDetail without select into?
-- RAISE NOTICE '(row) Product Detail: %', rProductDetail;

END;
$$ LANGUAGE plpgsql;
SELECT GetProductDetails(1);
SELECT GetProductDetails(2);
SELECT GetProductDetails(3);
Try it: PostgreSQL - OneCompiler -
Write, run and share PostgreSQL
code online
E
SELECT...INTO (cont)
In the case of a PLpgSQL statement like
K
select a into b from R where ...
If the selection returns no tuples
● the variable b gets the value NULL
If the selection returns multiple tuples
● the variable b gets the value from the first tuple
SELECT...INTO (cont)
An alternative to check for "no data found"
Use the special variable FOUND ...
● local to each function, set false at start of function
● set true if a SELECT finds at least one tuple
● set true if INSERT/DELETE/UPDATE affects at least one tuple
● otherwise, remains as FALSE
K
FOUND - example
CREATE TABLE Products (
ProductID SERIAL PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10,2),
Quantity INT
);
INSERT INTO Products (ProductName, Price, Quantity) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 15),
('Tablet', 600.00, 20),
('Monitor', 300.00, 12),
('Keyboard', 50.00, 30);
CREATE OR REPLACE FUNCTION GetProductDetails(first_pid INT, second_pid INT)
RETURNS VOID AS $$
DECLARE
vProductName VARCHAR(100);
vPrice DECIMAL(10,2);
vQuantity INT;
BEGIN
SELECT ProductName, Price, Quantity INTO vProductName, vPrice, vQuantity
FROM Products
WHERE ProductID = first_pid;

-- Check if the SELECT INTO statement found a row
IF FOUND THEN
-- If a product was found, output its details
RAISE NOTICE 'First Product. Product Name: %, Price: $%, Quantity: %', vProductName, vPrice, vQuantity;
ELSE
-- If no product was found, output a different message
RAISE NOTICE 'First Proudct. No product found with ProductID: %', first_pid;
END IF;

SELECT ProductName, Price, Quantity INTO vProductName, vPrice, vQuantity
FROM Products
WHERE ProductID = second_pid;

-- Check if the SELECT INTO statement found a row
IF FOUND THEN
-- If a product was found, output its details
RAISE NOTICE 'Second Product. Product Name: %, Price: $%, Quantity: %', vProductName, vPrice, vQuantity;
ELSE
-- If no product was found, output a different message
RAISE NOTICE 'Second Proudct. No product found with ProductID: %', second_pid;
END IF;
END;
$$ LANGUAGE plpgsql;
-- predict the outputs for the following calls
SELECT GetProductDetails(1, 2);
-- SELECT GetProductDetails(2, 6);
-- SELECT GetProductDetails(6, 1);
Try it: PostgreSQL - OneCompiler -
Write, run and share PostgreSQL
code online
E
Returning Multiple Values
PLpgSQL functions can return a set of values (setof Type)
● effectively a function returning a table
● Type could be atomic ⇒ like a single column
● Type could be tuples ⇒ like a full table
K
integer, float, numeric, date, text, varchar(n), ...
Atomic types, e.g.
create type Point as (x float, y float);
Tuple types, e.g.
INSERT ... RETURNING
Can capture values from tuples inserted into DB:
K
insert into Table(...) values
(Val1, Val2, ... Valn)
returning ProjectionList into VarList
Can help to save one query statement.
PLpgSQL Mechanism
Some things to beware of:
● doesn't provide any i/o facilities (except RAISE NOTICE)
○ the aim is to build computations on tables that SQL alone can't do
● functions are not syntax-checked when loaded into DB
○ you don't find out about the syntax error until "run-time"
● error messages are sometimes not particularly helpful
● functions are defined as strings
○ change of "lexical scope" can sometimes be confusing
● giving params/variables the same names as attributes
○ can avoid by starting all param/var names with underscore
K
Summary: debugging PLpgSQL can sometimes be tricky.
Debugging Output
Printing info about intermediate states is critical for debugging
Depending on how PostgreSQL is configured
K
-- assuming x==3, y==3.14, z='abc'
raise notice 'x+1 = %, y = %, z = %', x+1, y, z;
-- displays "NOTICE: x+1 = 4, y = 3.14, z = abc"
● raise notice allows you to display info from a function
● displayed in psql window during the function's execution
● usage: raise notice 'FormatString',Value1,...Valuen;
Exceptions
PLpgSQL supports exception handling via
K
begin
Statements...
exception
when Exceptions1 then
StatementsForHandler1
when Exceptions2 then
StatementsForHandler2
...
end;
Each Exceptionsi is an OR list of exception names, e.g.
division_by_zero OR floating_point_exception OR ...
Exceptions (cont)
When an exception occurs:
● control is transferred to the relevant exception handling code
● all database changes so far in this transaction are undone
● all function variables retain their current values
● handler executes and then transaction aborts (and function exits)
If no handler in current scope, exception passed to next outer level.
Default exception handlers, at outermost level, exit and log error.
K
Exceptions (cont)
The raise operator can generate server log entries, e.g.
K
raise debug1 'Simple message';
raise notice 'User = %',user_id;
raise exception 'Fatal: value was %',value;
There are several levels of severity:
● DEBUG1, LOG, INFO, NOTICE, WARNING, and EXCEPTION
● not all severities generate a message to the client (psql)
Location:
● …/pgsql/Log
Exception - example
CREATE TABLE Products (
ProductID SERIAL PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10,2),
Quantity INT
);
INSERT INTO Products (ProductName, Price, Quantity) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 15),
('Tablet', 600.00, 20),
('Monitor', 300.00, 12),
('Keyboard', 50.00, 30);
CREATE OR REPLACE FUNCTION UpdateProductPrice(p_ProductID INT, p_NewPrice DECIMAL(10,2))
RETURNS VOID AS $$
BEGIN
-- Attempt to update the product's price
UPDATE Products SET Price = p_NewPrice WHERE ProductID = p_ProductID;

-- Check if the update took place
IF NOT FOUND THEN
RAISE EXCEPTION 'Product with ID % does not exist.', p_ProductID;
END IF;

EXCEPTION
WHEN OTHERS THEN
-- Handle any exceptions that occur
RAISE NOTICE 'An error occurred: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;
-- try to observe for the outputs
-- SELECT UpdateProductPrice(1, 1250.00);
-- SELECT *
-- FROM Products
-- WHERE ProductID = 1;
-- SELECT UpdateProductPrice(999, 1250.00);
CREATE OR REPLACE FUNCTION UpdateProductPriceAndDivide(p_ProductID INT, p_NewPrice DECIMAL(10,2), p_Divisor DECIMAL)
RETURNS VOID AS $$
BEGIN


-- Simulate a calculation that might fail
p_NewPrice := p_NewPrice / p_Divisor;

-- Attempt to update the product's price
UPDATE Products SET Price = p_NewPrice WHERE ProductID = p_ProductID;

IF NOT FOUND THEN
RAISE EXCEPTION 'Product with ID % does not exist.', p_ProductID;
END IF;

EXCEPTION
WHEN DIVISION_BY_ZERO THEN
RAISE NOTICE 'Division by zero attempted in price update for product ID %.', p_ProductID;
WHEN OTHERS THEN
RAISE NOTICE 'An unexpected error occurred: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;
-- try to observe for the outputs
-- SELECT UpdateProductPriceAndDivide(1, 1250.00, 0);
-- SELECT *
-- FROM Products
-- WHERE ProductID = 1;
-- SELECT UpdateProductPriceAndDivide(999, 1250.00, 2);
Try it: PostgreSQL - OneCompiler -
Write, run and share PostgreSQL
code online
E
Query results in PLpgSQL
Can evaluate a query and iterate through its results
one tuple at a time, using a for ... loop
K
declare
tup Type;
begin
for tup in Query
loop
Statements;
end loop;
end;
Type of tup variable must
match type of Query results
If declared as record, will
automatically match Query
results type
Query Results - example
CREATE TABLE Employees (
eid SERIAL PRIMARY KEY,
name VARCHAR(100),
salary INTEGER
);
INSERT INTO Employees (name, salary) VALUES
('Alice', 50000),
('Bob', 60000),
('Charlie', 70000),
('Dana', 80000),
('Evan', 90000),
('Faith', 55000),
('George', 75000),
('Hannah', 85000);
create or replace function
count_well_paid(_minsal integer) returns integer
as $$
declare
nemps integer := 0;
tuple record;
begin
for tuple in
select name,salary from Employees
loop
if (tuple.salary > _minsal) then
nemps := nemps + 1;
end if;
end loop;
return nemps;
end;
$$ language plpgsql;
-- Can we optimize the function a bit? --hint: using where clause
-- Can we further optmize the function? --hint using aggregation
SELECT count_well_paid(60000);
SELECT count_well_paid(80000);
Try it: PostgreSQL - OneCompiler -
Write, run and share PostgreSQL
code online
E
Dynamically Generated Queries
EXECUTE takes a string and executes it as an SQL query.
Examples:
K
-- constant string
execute 'select * from Employees';
-- concatenation of constant strings
execute 'select * from ' || 'Employees';
-- using a name of e.g. table or attribute
execute 'select * from ' || quote_ident($1);
-- using a value generated in the program
execute 'delete from Accounts' || ' where holder=' || quote_literal($1);
Dynamic Queries - example
CREATE TABLE Employees (
eid SERIAL PRIMARY KEY,
name VARCHAR(100),
salary INTEGER
);
INSERT INTO Employees (name, salary) VALUES
('Alice', 50000),
('Bob', 60000),
('Charlie', 70000),
('Dana', 80000),
('Evan', 90000),
('Faith', 55000),
('George', 75000),
('Hannah', 85000);
CREATE OR REPLACE FUNCTION count_something(attr TEXT, _minval INTEGER) RETURNS INTEGER AS $$
DECLARE
nemps INTEGER;
BEGIN
-- Construct and execute a dynamic SQL statement
EXECUTE 'SELECT COUNT(*) FROM Employees WHERE ' || attr || ' > ' || _minval::TEXT
INTO nemps;

RETURN nemps;
END;
$$ LANGUAGE plpgsql;
-- try to understand the function
-- try to predict the results
SELECT count_something('salary', 60000);
SELECT count_something('eid', 2);
Try it: PostgreSQL - OneCompiler -
Write, run and share PostgreSQL
code online
E
Thank you!