#Lab Session 5 - An Introduction to Building Database Stored Procedures in PL/pgSQL

###By Mitchell Welch

### University of New England

---

## Reading
* Chapter 10 from ***Fundamentals of Database Systems*** by Elmazri and Navathe

---

## Summary
* House Keeping 
* Introducing PL/pgSQL
* PL/pgSQL Basics


---
## House Keeping

* In todays practical session we will be working with a PostgrSQL database to develop some stored procedures. 
* As usual, we will be working with a pre-built database in our examples and exercises.
* Today we are going to work with the *Book Town* database that you will be working within in your practical assignments.
    * This practical session will allow you to become familiar with its structure. 
* Start by downloading the .sql script that contains the DDL statements and the data for the Book Town database from: [http://turing.une.edu.au/~cosc210/assignments/a2/p1_book_town.sql](http://turing.une.edu.au/~cosc210/assignments/a2/p1_book_town.sql)  


* Using the `createdb` utility, create a new database for practical session 5. (Note you will not need to connect to this database using an external program so you can just your standard UNE account to create this database):

* Once created, log into your database and check that it was created correctly:

```

[mwelch8@turing prac_5]$ createdb mwelch8_prac_5
[mwelch8@turing prac_5]$ psql mwelch8_prac_5
psql (9.4.4)
Type "help" for help.

mwelch8_prac_5=> \dt
No relations found.
mwelch8_prac_5=> 


``` 

* Now you can import the Book Town database (Replace my file path with your file location):

```

mwelch8_prac_5=> \i /home/mwelch8/Documents/COSC210/prac_ass_1/p1_book_town.sql
CREATE TABLE
COPY 18
CREATE TABLE
COPY 16
CREATE TABLE
COPY 15
CREATE TABLE
COPY 13
CREATE TABLE
COPY 17
CREATE TABLE
COPY 16
CREATE TABLE
COPY 31
CREATE SEQUENCE
CREATE TABLE
COPY 36
mwelch8_prac_5=> \dt
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | authors    | table | mwelch8
 public | books      | table | mwelch8
 public | customers  | table | mwelch8
 public | editions   | table | mwelch8
 public | publishers | table | mwelch8
 public | shipments  | table | mwelch8
 public | stock      | table | mwelch8
 public | subjects   | table | mwelch8
(8 rows)

mwelch8_prac_5=>

```

* Throughout this practical session, you should run each of the example queries and review the results returned from the Book Town database.

* Now you are ready for the prac.



---

## Introducing PL/pgsql

In the previous lab session, we looked at connecting custom developed programs up to the PostrgreSQL DBMS to develop database-driven applications.This approach allows you to use a general-purpose programming language to develop software that interacts with the data in the database.

* For some applications, it may make sense to do parts of the the processing *within* the database management system. This is achieved by constructing *stored procedures* within the database management system. 
    * PostgreSQL provides the PL/pgSQL procedural language for defining stored procedures that can process data within a postgres database: [http://www.postgresql.org/docs/9.4/static/plpgsql-overview.html](http://www.postgresql.org/docs/9.4/static/plpgsql-overview.html)  

SQL is the language PostgreSQL (and most other relational databases) use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server.

That means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All these communications and will incur network overhead if your client is on a different machine than the database server.

With PL/pgSQL, you can create procedures (which can include multiple database queries) that can be executed on the database server. This provides the power of a procedural language and the ease of use of SQL, but with considerable savings on the client/server communication overhead.

* Extra round trips between client and server are eliminated.

* Intermediate results that the client does not need, do not have to be marshalled or transferred between server and client.

* Multiple rounds of query parsing can be avoided.

* This can result in a considerable performance increase as compared to an application that does not use stored functions.

(Source: [http://www.postgresql.org/docs/9.4/static/plpgsql-overview.html](http://www.postgresql.org/docs/9.4/static/plpgsql-overview.html))

* PL/pgSQL can be used to create custom functions that can be called within the database. The function can make use ofprocedural control structures that (e.g. loops, selection etc.) you can find in most general-purpose procedural programming languages such as Java/Python/C/C++.

## PL/pgSQL Basics

* Procedures in PL/pgSQL are defined using DDL statements, in a similar way to tables, views etc. 
* Procedures can be created for commonly executed tasks.
* The basic definition of a PL/pgSQL function has the following form:

```sql

CREATE FUNCTION <<function_name>>([parameter_name, type]) [RETURNS <<type>>] AS $$

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

$$ LANGUAGE plpgsql;


```

In this DDL statement, the `DECLARE` block contains declarations of variables that can be used within the procedural statements inside the `BEGIN-END` block. A label is only needed if you want to identify the block for use in an EXIT statement, or to qualify the names of the variables declared in the block. If a label is given after END, it must match the label at the block's beginning.

 Now we will look at our first example. Below is our 'Hello World' example:
 
```sql

CREATE FUNCTION hello() RETURNS varchar AS $$

BEGIN
    RETURN 'Hello,World';
END;
$$ LANGUAGE plpgsql; 

```
 
 In this example, the function returns a varchar type value containing the 'Hello,World' string.
 
 We can run the function using a simple `SELECT` statement:
 
```
postgres=# SELECT hello();                                                         
-------------
Hello,World
(1 row)

postgres=# 
 
```
 
We can see that `SELECT` clause returns a single column that contains the result of the function call. Create the `hello()` function in your prac_5 database and run the function using a select statement.

In the following example, we look at declaring some simple integer variables, updating their value and returning a result of performing the addition operation on the two variables. 

```sql

CREATE FUNCTION add_func() RETURNS integer AS $$
DECLARE
    value_1 integer := 30;
    value_2 integer := 25;
    value_3 integer := 0;
BEGIN
    value_1 := 10;
    value_2 := 5;
    value_3 := value_1 + value_2;

    RETURN value_3;
END;
$$ LANGUAGE plpgsql;

```

Notice that in PL/pgSQL the `:=` is used as the assignment operator. Like in the table `CREATE` statements, the types for the variables are specified after the variable names.

Run this function using a `SELECT` statement:

```
postgres=# SELECT add_func();
 add_func 
----------
       15
(1 row)

postgres=# 

```

* We can pass parameters into our functions using a similar syntax to most standard programming languages.
* In this example we create a function that returns the sum of two numbers that are passed  in as parameters:

```sql

CREATE FUNCTION sum_of_two_numbers(m integer, n integer) 
RETURNS integer AS $$
BEGIN
    RETURN m + n;
END;
$$ LANGUAGE plpgsql;

```
* In this example, the `RETURNS` clause has been included to specify that an integer is returned by the function.

```
mwelch8_prac_5=> SELECT sum_of_two_numbers(1,4);
 sum_of_two_numbers 
--------------------
                  5
(1 row)

mwelch8_prac_5=> 

```

* PL/pgSQL Procedures can execute queries and manipulate data in the database.
* The following function returns the full name of an author by the author_id
* A variable of a composite type is called a *row variable*. A *row variable* which can hold a whole row from a SELECT or FOR query result, so long as that query's column set matches the declared type of the variable.
* This is implemented using the syntax:

```sql

name table_name%ROWTYPE;

```

Example:

```sql

CREATE FUNCTION get_author (integer) RETURNS text AS $$
  DECLARE
    auth_id ALIAS FOR $1;
    found_author authors%ROWTYPE;
  BEGIN
   SELECT INTO found_author * FROM authors WHERE author_id = auth_id;
    RETURN found_author.first_name || ' ' || found_author.last_name;
  END;
$$ LANGUAGE 'plpgsql';

```

```
mwelch8_prac_5=> select get_author(16);
    get_author     
-------------------
 Louisa May Alcott
(1 row)

```

* In this more complicated example, we search for a specific author by name and throw an exception if the author is not returned:

```sql

CREATE FUNCTION test1(fst_name text) RETURNS text AS $$
DECLARE
    found_author authors%ROWTYPE;
BEGIN
    SELECT * INTO found_author FROM authors WHERE first_name = fst_name;
IF NOT FOUND THEN
    RAISE EXCEPTION 'author % not found', fst_name;
END IF;
RETURN 'OK';
END;
$$ LANGUAGE 'plpgsql';  

```

## PL/pgSQL Control Structures

* PL/pgSQL proves selection and iteration structures for implementing the logic within a procedure.

* `IF` and `CASE` are two conditional statements and they are used on certain conditions. Here is the syntax of IF statements (three forms) :

```sql
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSIF ... THEN ... ELSE
```

* Two forms of CASE syntax :

```sql
CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE
```


* IF-THEN

```sql
IF boolean-expression THEN
    statements
END IF;
```

* The following example uses the date functions and an IF-THEN-ELSE statement to determine if current date is the first of the month


```sql

CREATE FUNCTION day_one (currentdate date) RETURNS text AS $$   
BEGIN   
   IF EXTRACT(DAY FROM currentdate) = 1   
   THEN     
      RETURN '1st day of the Month';   
   ELSE   
      RETURN 'Other day';   
   END IF;   
END;   
$$   
LANGUAGE plpgsql;

```

```

mwelch8_prac_5=> SELECT day_one(current_date);
  day_one  
-----------
 Other day
(1 row)

mwelch8_prac_5=> SELECT day_one('1/1/2015');
       day_one        
----------------------
 1st day of the Month
(1 row)

mwelch8_prac_5=> 

```

* The CASE statement provides a means to test for equality and/or the presence of items in lists.


```sql

CREATE FUNCTION odd_or_even (x integer) RETURNS text AS $$   
DECLARE  
msg text;  
BEGIN   
CASE  
    WHEN x IN(2,4,6,8,10) THEN  
        msg := 'value even number';  
    WHEN x IN(3,5,7,9,11) THEN  
        msg := 'value is odd number';  
END CASE;  
RETURN msg;  
END;   
$$   
LANGUAGE plpgsql;  

```

* In this example, we use the `IN` operator to check which list of numbers the parameter `x` resides in.
* The selection structures can be nested and combined to implement the logic required within your procedure, in a similar manner to other programming languages.

Iteration is provided through the `LOOP`, `WHILE` and `FOR` statements

The keyword LOOP is used to start a basic, unconditional loop within a function. The basic job of an unconditional loop is to execute the statements within its body until it reach to an EXIT statement. To reach to an EXIT statement, the EXIT keyword is required along with WHEN, and followed by and an expression which holds the condition to reach the EXIT from a loop.

```sql
 LOOP
    statement;
    [...]
    EXIT [ label ] [ WHEN condition ];
  END LOOP;
```
* In this example, the loop iterates until the cube of the input parameter is >= 10000;


```sql

CREATE FUNCTION my_cube(integer) 
RETURNS integer AS $$
  DECLARE
    nm ALIAS FOR $1;
    cub integer;
  BEGIN
    cub := nm;
    LOOP
      cub := cub * cub * cub;
      EXIT WHEN cub >= 10000;
    END LOOP;
    RETURN cub;
  END;
$$ LANGUAGE 'plpgsql';

```

```

mwelch8_prac_5=> SELECT my_cube(3);
 my_cube 
---------
   19683
(1 row)

mwelch8_prac_5=> 

```

* It is important to correctly define the exit condition, otherwise your function will never exit!

* The `WHILE` and `FOR` loops are usually more useful control structures.

The WHILE loop is used to do the job repeatedly within the block of statements until the condition becomes false. In this type of loop, the condition mentioned will be executed first before the statement block is executed.

Here is the syntax of the WHILE loop:

```sql
[ <<label>> ]    
WHILE condition LOOP      
statement;      
[...]    
END LOOP;
```

Here we present an alternate implementation of the `my_cube(integer)` function using a `WHILE` loop:

```sql

CREATE FUNCTION while_cube(integer) 
RETURNS integer AS $$
  DECLARE
   nm ALIAS FOR $1;
    cub INTEGER;
  BEGIN
   cub:=nm;
    WHILE cub <=10000 LOOP
      cub := cub * cub * cub;
    END LOOP;
    RETURN cub;
  END;
$$ LANGUAGE 'plpgsql';


```

* Use the FOR loop to repeat a specific statement(s) within a block over a range specified terms.

In a PL/pgSQL `FOR` loop it is needed to initial an integer variable , to track the repetition of the loop, then the integer final value is given, and finally a statement block is provided within the loop.

Here is the syntax of the `FOR` loop:

```sql
  [ <<label>> ]
  FOR identifier IN [ REVERSE ] expression1 .. expression2  LOOP
      statement;
      [...]
  END LOOP;
```

* In this example, we iterate through all authors and return a string containing all of their first names concatenated together.



```sql

CREATE FUNCTION author_names() RETURNS text AS $$
  DECLARE
    output_txt TEXT :='\n';
    row_data authors%ROWTYPE;
  BEGIN
    FOR row_data IN SELECT * FROM authors ORDER BY first_name LOOP
      output_txt := output_txt || row_data.first_name || row_data.last_name || '\n';
    END LOOP;
    RETURN output_txt;
  END;
$$ LANGUAGE 'plpgsql';

```

* I'll let you run this one - the output is ugly.


## Some Exercises for You

####Exercise 1

Construct a PL/pgSQL function called 'extract\_title' that accepts a single integer-type parameter containing the subject\_id and returns a sting that contains a comma separated list of the titles of all books that correspond to the subject id in alphabetical order. Below is a template to get you started:

```sql
CREATE FUNCTION "extract_title" (integer) RETURNS text AS $$
  DECLARE
    sub_id ALIAS FOR $1;
    -- Declare a TEXT type variable called 'text_output' to store your output
    row_data RECORD;
  BEGIN
    FOR row_data IN SELECT * FROM books
    WHERE subject_id = sub_id ORDER BY title LOOP
      -- Your bit goes here!
    END LOOP;
    RETURN text_output;
  END;
$$ LANGUAGE 'plpgsql';

```



####Exercise 2

Construct a PL/pgSQL function called 'in\_stock' that accepts two integer parameters that contain the book_id and the edition and returns a boolean value (TRUE/FALSE) indicating if the book is in stock. below is a template for your function:

```sql

CREATE FUNCTION "in_stock" (integer,integer) RETURNS boolean AS $$
  DECLARE
    b_id ALIAS FOR $1;
    b_edition ALIAS FOR $2;
    b_isbn TEXT;
    stock_amount INTEGER;
  BEGIN
     -- This SELECT INTO statement retrieves the ISBN
     -- number of the row in the editions table that had
     -- both the book ID number and edition number that
     -- were provided as function arguments.
    SELECT INTO b_isbn isbn FROM editions ... -- Your bit!
 
     -- Check to see if the ISBN number retrieved
     -- is NULL.  This will happen if there is not an
     -- existing book with both the ID number and edition
     -- number specified in the function arguments.
     -- If the ISBN is null, the function returns a
     -- FALSE value and ends.
    IF b_isbn IS NULL THEN
      RETURN ..... -- Your bit!
    END IF;
 
     -- Retrieve the amount of books available from the
     -- stock table and record the number in the
     -- stock_amount variable.
    SELECT INTO stock_amount stock FROM stock WHERE .... --Your Bit!
 
     -- Use an IF/THEN/ELSE check to see if the amount
     -- of books available is less than, or equal to 0.
     -- If so, return FALSE.  If not, return TRUE.
    IF stock_amount <= 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END;
$$ LANGUAGE 'plpgsql';



```