Programming in postgre SQL with PLpg SQL Procedural

Programming in postgre. SQL with PL/pg. SQL Procedural Language extension to postgre. SQL

Consider the relation Point(X, Y) Q: How would you query the coordinates of all points situated on the curve y=x 2+3 x+5 ? A: Select x, y from point where y=x*x+3*x+5 Q: How would you query the coordinates of all pairs of points with a distance> 5 ? A: select p 1. x, p 1. y, p 2. x, p 2. y from point p 1, point p 2 where (p 1. y-p 2. y)*(p 1. y-p 2. y)+(p 1. x-p 2. x)*(p 1. xp 2. x)>25

Consider the relation Point(X, Y) Q: Suppose you have another relation, called edge(point 1, point 2). 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. .

PL/pg. SQL • 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/pg. SQL code in a regular file, for example first. Pl. sql, and load it with i in the psql console.

PL/pg. SQL Blocks PL/pg. SQL 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

Labeling a function: Create [or replace] function func. Name(var. Name 1 var. Type 1, var. Name 2 var. Type 2, …) Returns return. Var. Type AS $$ And at the end of the function: $$ language plpgsql; Example: Create or replace function my. Multiplication(var 1 integer, var 2 integer) returns integer as $$ BEGIN return var 1*var 2; END; $$ language plpgsql

Alternatively, the return value and type can be declared as function parameters: Create [or replace] function func. Name(var. Name 1 var. Type 1, var. Name 2 var. Type 2, …, out ret. Var. Name retvar. Type) AS $$ Example: Create or replace function my. Addition(var 1 integer, var 2 integer, out add. Res integer) returns integer as $$ BEGIN add. Res: =var 1+var 2; END; $$ language plpgsql This allows returning more than one value without defining a record

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 tablename%ROWTYPE;

first. sql: Example Create or replace function add. Tax(price real, OUT res 1 real) as $$ begin res 1: = price*1. 155; end; $$language plpgsql; In the psql console write: i first. sql Then you can use the function: Insert into prices. Table values(add. Tax(20)); or Select (add. Tax(20));

Declaring Variables with the %TYPE Attribute Examples DECLARE sname fav_boat my_fav_boat. . . Accessing column sname in table Sailors. sname%TYPE; VARCHAR(30); fav_boat%TYPE : = 'Pinta'; Accessing a variable

Declaring Variables with the %ROWTYPE Attribute Declare a variable with the type of a ROW of a table. reserves_record Accessing table Reserves%ROWTYPE; And how do we access the fields in reserves_record? reserves_record. sid=9; Reserver_record. bid=877;

Select into Create or replace function my. Sp(var 1 integer) returns integer as $$ declare sp_var sportsman%rowtype; BEGIN select * into sp_var from sportsman; return sp_var. age*var 1; 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

Select into strict Create or replace function my. Sp(var 1 integer) returns integer as $$ declare sp_var sportsman%rowtype; BEGIN select * into strict sp_var from sportsman; return sp_var. age*var 1; END; language plpgsql $$ • In this case, if more or less than one row is returned, a run-time error will occur

CREATE or replace FUNCTION my. Mult(t 2_row multipliers) RETURNS real AS $$ declare t_row sportsman%rowtype; BEGIN SELECT * INTO t_row FROM sportsman WHERE age<30 order by age desc; RETURN t_row. age*t 2_row. mult; END; LANGUAGE plpgsql; $$ select my. Mult(ms. *) from multipliers ms where ms. mult>100 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

Checking if a row was returned Declare my. Var sportsman%rowtype; Begin Select * into my. Var from sportsman where age=4; If not found then…

Conditioning IF boolean-expression THEN statements END IF; … IF v_age > 22 THEN UPDATE sportsman SET salary = salary+1000 WHERE sid = v_sid; END IF; …

Conditioning 2 IF boolean-expression THEN statements ELSE statements END IF;

Conditioning 3 IF boolean-expression THEN statements ELSIF boolean-expression THEN statements … ELSE statements END IF ;

Example CREATE or replace FUNCTION assess. Rate(rating real) RETURNS text AS $$ BEGIN if rating>9 then return 'great'; elsif rating>7 then return 'good'; elsif rating>5 then return 'keep on working'; elsif rating>3 then return 'work harder!'; else return 'you can stop working'; end if; END; $$ LANGUAGE plpgsql; Select assess. Rate(6. 7);

Suppose we have the following table: create table mylog( who text, num_run integer ); • 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 mylog who num_run Peter 3 John 4 Moshe 2

Solution CREATE FUNCTION update. Logged() RETURNS void AS $$ DECLARE cnt integer; BEGIN Select count(*) into cnt from mylog where who=user; If cnt>0 then update mylog set num_run = num_run + 1 where who = user; else insert into mylog values(user, 1); end if; end; $$ LANGUAGE plpgsql;

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 …

LOOP some computations -IF count > 0 THEN EXIT; END IF; END LOOP; LOOP some computations -EXIT WHEN count > 0; END LOOP; BEGIN some computations -IF stocks > 100000 THEN EXIT; END IF; END; Examples

Continue • The next iteration of the loop is begun $$ Create or replace function my. Test(var 1 integer) returns integer as DECLARE i integer; BEGIN i: =1 loop exit when i>var 1 i=i+1 continue when i<20 raise notice 'num is %', i end loop return i*var 1 END $$ language plpgsql What does this print for select my. Test(30)? 20… 31

While loop WHILE expression LOOP --statements END LOOP ; WHILE money_amount > 0 AND happiness<9 LOOP buy more -END LOOP;

For loop FOR var IN [ REVERSE ] st. Range. . end. Range [ BY jumps ] LOOP statements FOR i IN 1. . 10 LOOP END 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;

Looping Through Query Results FOR target IN query LOOP statements END LOOP; CREATE or replace FUNCTION assess. Rates() RETURNS void AS $$ DECLARE i record; BEGIN for i in select rating from ratings order by rating loop if i. rating>9 then raise notice 'great'; elsif i. rating>7 then raise notice 'good'; elsif i. rating>5 then raise notice 'keep on working'; elsif i. rating>3 then raise notice 'work harder!'; else raise notice 'you can stop working'; end if; end loop; END; $$ LANGUAGE plpgsql;

Trapping exceptions DECLARE declarations BEGIN statements EXCEPTION WHEN condition [ OR condition. . . ] THEN handler_statements. . . END;

Create or replace function errors(val integer) returns real as $$ Declare val 2 real; BEGIN val 2: =val/(val-1); return val 2; Exception when division_by_zero then raise notice 'caught a zero division'; return val 2; End; $$ LANGUAGE plpgsql;

Errors and messages RAISE DEBUG RAISE LOG RAISE INFO RAISE NOTICE RAISE WARNING RAISE EXCEPTION.

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/pg. SQL • The function must not take arguments and returns type trigger • First we create a trigger function and then create the trigger using create trigger

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

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

Writing a trigger function When a trigger is fired, several variables are automatically created: • New • Old • TG_OP • …

CREATE FUNCTION to. Upper() RETURNS trigger AS $$ BEGIN new. sname : = UPPER(new. sname); END; $$ LANGUAGE plpgsql; CREATE TRIGGER to. Upper. Trig BEFORE INSERT or UPDATE on sportsman FOR EACH ROW execute procedure to. Upper();

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

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

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

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;

CREATE TRIGGER emp_backup AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_backup ();

Statement Trigger CREATE FUNCTION shabbat_trig_func() RETURNS trigger AS $$ BEGIN if (TO_CHAR(current_date, 'DY')='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();