---
title: Database Systems Pre-Lab 6 Part I
---
<h1 style='border: none'><center>Database Systems Pre-Lab 6 Part I</center></h1>
<h2 style='border: none'><center>Functions & procedures</center></h2>
<h5><center>The Islamic University of Gaza<br>Engineering Faculty<br>Department of Computer Engineering</center></h5>
<h6>Authors: Usama R. Al Zayan & Rasha E. Kahil<span style="float:right">2023/03/24</span></h6>
---
## Expecteed outcomes
* To be introduced to Database Functions commands and concepts.
* To learn how to create and delete different types of Functions in PostgreSQL.
* To learn how to use Functions in PostgreSQL.
## Lab 5: Time and Plan
| Tasks | Timing |
| -------- | -------- |
| Quiz 6 | 10 min |
| Task 1 | 40 min |
| Task 2 | 40 min |
# Functions
As we studied in the textbook that functions and procedures allow business logic to be stored in the database and executed from SQL statements.
PostgreSQL provides four kinds of functions:
* query language functions (functions written in SQL).
* procedural language functions (functions written in, for example, PL/pgSQL or PL/Tcl).
* internal functions.
* C-language functions.
:::info
:information_source: In this lab we will study [SQL & PL/pgSQL] functions.
:::
## General syntax of creating a function
```sql
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name
| WINDOW
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} … [ WITH ( attribute [, ...] ) ]
```
## SQL functions
SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list, If the last query happens to return no rows at all, the null value will be returned.
Alternatively, an SQL function can be declared to return a set, by specifying the function's return type as SETOF *sometype*, or equivalently by declaring it as **RETURNS TABLE(columns)**.
Any collection of commands in the SQL language can be packaged together and defined as a function.
Besides **SELECT** queries, the commands can include data modification queries (**INSERT**, **UPDATE**, and **DELETE**), as well as other SQL commands.
**You cannot use transaction control commands, e.g. COMMIT, SAVEPOINT, and some utility commands, e.g. VACUUM, in SQL functions.**
However, the final command must be a SELECT or have a RETURNING clause that returns whatever is specified as the function's **return type**.
Alternatively, if you want to define a SQL function that performs actions but has no useful value to return, you can define it as returning void.
**For example**, this function removes rows with negative salaries from the emp table:
```sql=
CREATE FUNCTION clean_instructor() RETURNS void AS '
DELETE FROM instructor
WHERE salary < 0;
' LANGUAGE SQL;
SELECT clean_instructor();
```
The function body needs to be written as a string constant, It is usually most convenient to use dollar quoting for the string constant, but you can also use single quotation.
Arguments to the SQL function are referenced in the function body using the syntax **$n**: $1 refers to the first argument, $2 to the second, and so on.
If an argument is of a **composite type**, then the dot notation, e.g., `$1.name`, can be used to access attributes of the argument.
### SQL Functions on Base Types
#### Example 1: write SQL function that takes instructor ID and rase, and adds that rase to his salary, and returns the new salary.
```sql=
CREATE FUNCTION rase_instructor (varchar, numeric) RETURNS numeric AS $$
UPDATE instructor
SET salary = salary + $2
WHERE id = $1;
SELECT salary from instructor where id = $1;
$$ LANGUAGE SQL;
SELECT rase_instructor('63395', 1000.0);
```
We can also return the salary of instructor with one command, instead of using select after the update. Using `RETURNING`.
```sql=
CREATE OR REPLACE FUNCTION rase_instructor (varchar, numeric) RETURNS numeric AS
$body$
UPDATE instructor
SET salary = salary + $2
WHERE id = $1
RETURNING salary;
$body$ LANGUAGE SQL;
SELECT rase_instructor('63395', - 1000.0);
```
#### Example 2: write SQL function that takes department name and return the average salary for that department.
```sql=
CREATE FUNCTION avg_dep_salary(char) RETURNS numeric AS $$
SELECT avg(salary) from instructor where dept_name = $1 ;
$$ LANGUAGE SQL;
SELECT avg_dep_salary('Comp. Sci.');
```
### SQL Functions on Composite Types
When writing functions with arguments of composite types, we must not only specify which argument we want (as we did above with $1 and $2) but also the desired attribute (field) of that argument.
For example, our student table which contains student data, and therefore also the name of the composite type of each row of the table.
**Functions can take and return Composite Types.**
#### Example 3: write SQL function search_student takes student id and returns his info.
```sql=
create OR REPLACE FUNCTION search_student(char) RETURNS student AS $$
select * from student where id = $1;
$$ LANGUAGE SQL;
select search_student('11111');
```
#### Example 4: write SQL function register_student which takes student info, and insert him to the student table and returns his ID.
```sql=
create OR REPLACE FUNCTION register_student(student) RETURNS char AS $$
insert into student values($1.id ,$1.name ,$1.dept_name ,$1.tot_cred )
RETURNING $1.id;
$$ LANGUAGE SQL;
select register_student(row ('11313', 'Abdallah','Comp. Sci.',0));
```
**Also you can attach names to a function's parameters.**
#### Example 5: write SQL function takes instructor ID and new department name, update his old department name, and return the department name.
```sql=
CREATE FUNCTION update_instructor_dep(inst_id char, new_dep_name char)
RETURNS char AS $$
UPDATE instructor
SET dept_name = new_dep_name
WHERE id = inst_id
RETURNING dept_name;
$$ LANGUAGE SQL;
select update_instructor_dep ('4233','Languages');
```
**Functions can be declared with default values for some or all input arguments**
```sql
CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
```
### SQL Functions with Output Parameters
An alternative way of describing a function's results is to define it with output parameters.
#### Example 6:
```sql
CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
```
### SQL Functions Returning Sets
When an SQL function is declared as returning SETOF *sometype*, the function's final query is executed to completion, and each row it outputs is returned as an element of the result set
#### Example 7:
```sql=
CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
-----
CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;
SELECT * FROM sum_n_product_with_tab(10);
```
```
sum | product
-----+---------
11 | 10
13 | 30
15 | 50
17 | 70
```
### SQL Functions Returning TABLE
There is another way to declare a function as returning a set, which is to use the syntax RETURNS TABLE **(columns)**
#### Example 8:
```sql=
CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
|$$ LANGUAGE SQL;
```
**It is not allowed to use explicit OUT or INOUT parameters with the RETURNS TABLE notation. You must put all the output columns in the TABLE list.**
### To delete function
```sql
DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
[ CASCADE | RESTRICT ]
```
```sql=
DROP FUNCTION sqrt(integer);
```
## PL/pgSQL functions
Functions written in PL/pgSQL are defined to the server by executing CREATE FUNCTION commands.
```sql
CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;
```
**PL/pgSQL is a block-structured language. The complete text of a function body must be a block. is defined as:**
```sql
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
END [ label ];
```
#### Example9:
```sql=
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
quantity := 50;
-- Create a subblock
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
```
### Declarations
PL/pgSQL variables can have any SQL data type, such as integer, varchar, and char.
### General syntax of a variable declaration
```sql
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ]
[ { DEFAULT | := | = } expression ];
```
#### Some examples
```sql=
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
```
### Expressions
All expressions used in PL/pgSQL statements are processed using the server's main SQL executor.
```sql
IF expression THEN ...
```
#### Example 10:
```sql=
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
```
## Procedure
General syntax of creating Procedure.
```sql
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ]
[, ...] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
}
```
**Use `CALL` to execute a procedure.**
```sql=
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
CALL insert_data(1, 2);
```
### To delete procedure
```sql
DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype
[, ...] ] ) ] [, ...] [ CASCADE | RESTRICT ]
```
```sql=
DROP PROCEDURE do_db_maintenance();
```
###### tags: `Database Systems` `Pre-Lab` `IUG` `Computer Engineering`
<center>End Of Pre-Lab 6 Part I</center>
Sign in with Wallet
Connect another wallet