Programming%20in%20postgreSQL%20with%20PL/pgSQL

1
Programming in postgreSQL with PL/pgSQL
Procedural
Language
extension to
postgreSQL
2
Consider the relation Point(X,Y)

• Q How would you query the coordinates of all
  points situated on the curve yx23x5 ?
• A Select x,y from point where yxx3x5
• Q How would you query the coordinates of all
  pairs of points with a distancegt 5 ?
• A select p1.x, p1.y, p2.x, p2.y from point p1,
  point p2
• where
• (p1.y-p2.y)(p1.y-p2.y)(p1.x-p2.x)(p1.x-p2.x)gt2
  5

3
Consider the relation Point(X,Y)

• Q Suppose you have another relation, called
  edge(point1, point2). How would you query the
  coordinates of all points in the shortest path
  from (3,7) to (32,77)?
• A With standard SQL, you cannot..

4
PL/pgSQL

• Allows using general programming tools with SQL,
  for example loops, conditions, functions, etc.
• This allows a lot more freedom than general SQL
• We write PL/pgSQL code in a regular file, for
  example firstPl.sql, and load it with \i in the
  psql console.

5
PL/pgSQL Blocks

• PL/pgSQL code is built of Blocks, with a unique
  structure

• LABEL
• DECLARE (optional)
• / Variable declaration/
• BEGIN (mandatory)
• / Executable statements (what the block DOES!)/
• EXCEPTION (optional)
• / Exception handling/
• END (mandatory)
• LABEL

6

• Labeling a function
• And at the end of the function
• Example

Create or replace function funcName(varName1
varType1,varName2 varType2,) Returns
returnVarType AS
language plpgsql
Create or replace function myMultiplication(var1
integer, var2 integer) returns integer as
BEGIN return var1var2 END language
plpgsql
7

• Alternatively, the return value and type can be
  declared as function parameters

Create or replace function funcName(varName1
varType1,varName2 varType2,,out retVarName
retvarType) AS
Example
Create or replace function myAddition(var1
integer, var2 integer, out addRes integer)
returns integer as BEGIN addResvar1var2 END
language plpgsql
This allows returning more than one value without
defining a record
8
Declare
The general syntax of a variable declaration
is name CONSTANT type NOT NULL DEFAULT
expression Examples user_id integer
quantity numeric(5) url varchar(20) myrow
tablenameROWTYPE
9
Example
first.sql

• Create or replace function addTax(price real, OUT
  res1
• real) as
• begin
• res1 price1.155
• end
• language plpgsql

In the psql console write \i first.sql Then you
can use the function Insert into pricesTable
values(addTax(20)) or Select (addTax(20))
10
Declaring Variables with the TYPE Attribute

• Examples

Accessing column sname in table Sailors
DECLARE sname Sailors.snameTYPE
fav_boat VARCHAR(30) my_fav_boat fav_boat
TYPE 'Pinta' ...
Accessing a variable
11
Declaring Variables with the ROWTYPE Attribute

• Declare a variable with the type of a ROW of a
  table.
• And how do we access the fields in
  reserves_record?

Accessing table Reserves
reserves_record ReservesROWTYPE
reserves_record.sid9 Reserver_record.bid877

12
Select into
Create or replace function mySp(var1 integer)
returns integer as declare sp_var
sportsmanrowtype BEGIN select into sp_var
from sportsman return sp_var.agevar1 END
language plpgsql

• If select returns more than one result, the first
  row will be taken, or nulls if no rows were
  returned
• Notice that unless Order by was specified, the
  first row is not well defined

13
Select into strict
Create or replace function mySp(var1 integer)
returns integer as declare sp_var
sportsmanrowtype BEGIN select into strict
sp_var from sportsman return sp_var.agevar1 END
language plpgsql

• In this case, if more or less than one row is
  returned, a run-time error will occur

14
Record

• A record is similar to row-type, but we dont
  have to predefine its structure

DECLARE varRecord record BEGIN select into
varRecord from students
15
CREATE or replace FUNCTION myMult(t2_row
multipliers) RETURNS real AS declare t_row
sportsmanrowtype BEGIN SELECT INTO t_row
FROM sportsman WHERE agelt30 order by age desc
RETURN t_row.aget2_row.mult END LANGUAGE
plpgsql

• select myMult(ms.) from multipliers ms where
  ms.multgt100 order by mult asc

What does this return? The multiplication of the
smallest mult which is larger than 100 by the age
of the oldest sportsman whose age is less than 30
16
Checking if a row was returned

• Declare
• myVar sportsmanrowtype
• Begin
• Select into myVar from sportsman where age4
• If not found then

17
Conditioning

• IF boolean-expression
• THEN statements
• END IF

IF v_age gt 22 THEN UPDATE sportsman SET
salary salary1000 WHERE sid v_sid END IF
18
Conditioning 2

• IF boolean-expression
• THEN statements
• ELSE statements
• END IF

19
Conditioning 3

• IF boolean-expression
• THEN statements
• ELSIF boolean-expression
• THEN statements
• ELSIF boolean-expression
• THEN statements
• ELSE statements
• END IF

20
Example
CREATE or replace FUNCTION assessRate(rating
real) RETURNS text AS BEGIN if ratinggt9 then
return 'great' elsif ratinggt7 then return
'good' elsif ratinggt5 then return 'keep on
working' elsif ratinggt3 then return 'work
harder!' else return 'you can stop working' end
if END LANGUAGE plpgsql

• Select assessRate(6.7)

21
Suppose we have the following table
mylog
create table mylog( who text, num_run
integer )
num_run who
3 Peter
4 John
2 Moshe

• Want to keep track of how many times users have
  run a PL/SQL block
• When the block is run, if user is already in
  table, increment num_run. Otherwise, insert user
  into table

22
Solution
CREATE FUNCTION updateLogged() RETURNS void AS
DECLARE cnt integer BEGIN Select count() into
cnt from mylog where whouser If cntgt0 then
update mylog set num_run num_run 1
where who user else insert into mylog
values(user, 1) end if end LANGUAGE
plpgsql
23
Simple loop

• LOOP
• statements
• END LOOP
• Terminated by Exit or return
• Exit only causes termination of the loop
• Can be specified with a condition
• Exit when

24
Examples
LOOP -- some computations IF count gt 0 THEN
EXIT END IF END LOOP
LOOP -- some computations EXIT WHEN count gt 0
END LOOP
BEGIN -- some computations IF stocks gt 100000
THEN EXIT END IF END
25
Continue

• The next iteration of the loop is begun

Create or replace function myTest(var1 integer)
returns integer as DECLARE i
integer BEGIN i1 loop exit when
igtvar1 ii1 continue when ilt20 raise notice 'num
is ',i end loop return ivar1 END language
plpgsql
What does this print for select myTest(30)? 2031
26
While loop

• WHILE expression
• LOOP
• --statements
• END LOOP

WHILE money_amount gt 0 AND happinesslt9 LOOP --
buy more END LOOP
27
For loop

• FOR var IN REVERSE stRange ..endRange BY
  jumps
• LOOP
• statements
• END LOOP

FOR i IN 1..10 LOOP RAISE NOTICE 'i is ', i
END LOOP
FOR i IN REVERSE 10..1 LOOP -- some computations
here END LOOP
FOR i IN REVERSE 10..1 BY 2 LOOP RAISE NOTICE 'i
is ', i END LOOP
28
Looping Through Query Results

• FOR target IN query
• LOOP
• statements
• END LOOP

CREATE or replace FUNCTION assessRates() RETURNS
void AS DECLARE i record BEGIN for i in
select rating from ratings order by rating
loop if i.ratinggt9 then raise notice
'great' elsif i.ratinggt7 then raise notice
'good' elsif i.ratinggt5 then raise notice 'keep
on working' elsif i.ratinggt3 then raise notice
'work harder!' else raise notice 'you can stop
working' end if end loop END LANGUAGE
plpgsql
29
Trapping exceptions

• DECLARE
• declarations
• BEGIN
• statements
• EXCEPTION
• WHEN condition OR condition ... THEN
  handler_statements
• WHEN condition OR condition ... THEN
  handler_statements
• ...
• END

30
Create or replace function errors(val integer)
returns real as Declare val2
real BEGIN val2val/(val-1) return
val2 Exception when division_by_zero then raise
notice 'caught a zero division' return
val2 End LANGUAGE plpgsql
31
Errors and messages

• RAISE DEBUG
• RAISE LOG
• RAISE INFO
• RAISE NOTICE
• RAISE WARNING
• RAISE EXCEPTION.

32
Triggers

• A trigger defines an action we want to take place
  whenever some event has occurred.
• Can execute before or after the triggering event
• A triggering event can be an insert, update or
  delete
• The trigger can be defined to run once per
  changed row or once per statement
• The trigger function can be written in PL/pgSQL
• The function must not take arguments and returns
  type trigger
• First we create a trigger function and then
  create the trigger using create trigger

33
Triggers- cont.

• Row-level before triggers are usually used to
  modify or check the data that is changing
• Row-level after triggers are usually used to
  propagate the effect of the changes to other
  tables
• Pay attention to recursive trigger firing

34
Create trigger

• CREATE TRIGGER name BEFORE AFTER
  event OR ... ON table FOR
  EACH ROW STATEMENT
• EXECUTE PROCEDURE funcname ( arguments )

CREATE TRIGGER emp_trig BEFORE INSERT OR UPDATE
ON employee FOR EACH ROW EXECUTE PROCEDURE
emp_trig_func()
35
Writing a trigger function

• When a trigger is fired, several variables are
  automatically created
• New
• Old
• TG_OP

36
CREATE FUNCTION toUpper() RETURNS trigger AS
BEGIN new.sname UPPER(new.sname) END
LANGUAGE plpgsql
CREATE TRIGGER toUpperTrig BEFORE INSERT or
UPDATE on sportsman FOR EACH ROW execute
procedure toUpper()
37

• CREATE TABLE emp (empname text, salary integer,
  last_date timestamp, last_user text )

38

• CREATE FUNCTION emp_stamp() RETURNS trigger AS
  
• BEGIN
• -- Check that empname and salary are given
• IF NEW.empname IS NULL THEN RAISE EXCEPTION
  'empname cannot be null'
• END IF
• IF NEW.salary IS NULL THEN RAISE EXCEPTION '
  cannot have null salary', NEW.empname
• END IF
• IF NEW.salary lt 0 THEN RAISE EXCEPTION ' cannot
  have a negative salary', NEW.empname
• END IF
• NEW.last_date current_timestamp
• NEW.last_user current_user
• RETURN NEW
• END LANGUAGE plpgsql

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE
ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp()

39
CREATE TABLE emp ( empname text NOT NULL, salary
integer )
CREATE TABLE emp_backup( operation char(1) NOT
NULL, stamp timestamp NOT NULL, userid text NOT
NULL, empname text NOT NULL, salary integer )
40

• CREATE OR REPLACE FUNCTION process_emp_backup()
  RETURNS TRIGGER AS
• BEGIN
• IF (TG_OP 'DELETE') THEN
• INSERT INTO emp_backup
• SELECT 'D', now(), user, OLD.
• RETURN OLD
• ELSIF (TG_OP 'UPDATE') THEN
• INSERT INTO emp_backup
• SELECT 'U', now(), user, NEW.
• RETURN NEW
• ELSIF (TG_OP 'INSERT') THEN
• INSERT INTO emp_backup
• SELECT 'I', now(), user, NEW.
• RETURN NEW
• END IF
• RETURN NULL
• END LANGUAGE plpgsql

41
CREATE TRIGGER emp_backup AFTER INSERT OR UPDATE
OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE
process_emp_backup()
42
(No Transcript)
43
Statement Trigger
CREATE FUNCTION shabbat_trig_func() RETURNS
trigger AS BEGIN if (TO_CHAR(current_date,'D
Y')'SAT') then raise exception no work on
shabbat! end if Return END LANGUAGE
plpgsql
CREATE TRIGGER no_work_on_shabbat_trig BEFORE
INSERT or DELETE or UPDATE on sportsman for each
statement execute procedure shabbat_trig_func()