# SQL - Procedural Programming with PL/PGSQL

## Overview of pl/pgsql

- PL/PGSQL is a good example of a programming language tightly
  integrated with a database, allowing strong DB level of type and
  error checking and communication.

- You may find similar programming languages in other DBMSs that
  communicate with the database and SQL in a similar way. However,
  these are not standard languages.

  See full documentation here:

  <http://www.postgresql.org/docs/17/plpgsql.html>

- pl/pgsql supports the same data types as the database

- Programs and functions can be compiled and used directly at the db
  server

- Main pl/pgsql block has the form:

  ```
  [ <<label>> ]
  [DECLARE
  variable declarations ]
  BEGIN
  statement
  END [ label ] ;
  ```

- Variable types:

  ```
  integer
  numeric(5)
  varchar
  tablename%ROWTYPE
  tablename.columname%TYPE
  RECORD
  ```

  ROWTYPE and RECORD have subfields, i.e. x.name.

### Programming constructs

- Conditionals:

  ```
  IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
  Loops:
  [ <<label>> ]
  LOOP
  statements
  END LOOP [ label ];
  ```

- Returning a value:

  - pl/pgsql functions do not allow you to modify input variables
  - RETURN will return a value. As a result, you can call it like a
    constant in the select statement shown below:

  ```
  CREATE FUNCTION sales_tax(subtotal real, state varchar) RETURNS real AS $$
  DECLARE
     adjusted_subtotal real ;
  BEGIN
     IF state = 'NY' THEN
         adjusted_subtotal = subtotal * 0.08 ;
     ELSIF state = 'AL' THEN
         adjusted_subtotal = subtotal ;
     ELSE
         adjusted_subtotal = subtotal * 0.06;
     END IF ;
     RETURN adjusted_subtotal ;
  END ;
  $$ LANGUAGE plpgsql ;
  ```

- Now, test it:

  ```
  select sales_tax(100, 'NY') ;

  sales_tax
  -----------
  8
  (1 row)
  ```

- Note: The whole body of the function is entered within the two \$\$ signs.

- You can also return a table of rows:

  > - Return each tuple with RETURN NEXT and finish with RETURN
  > - As these return a table, they are called in the FROM clause. See the
  >   loop section below for examples.

- Starting with Version 11, Postgresql also allows procedures that modify
  input variables. You can read about them in the documentation.

### Handling SQL:

- We will consider three types of SQL expressions:

  1. Statements that return no output but a status (successful or not,
     and what was wrong)
  2. Statements that return a single tuple
  3. Statements that return multiple tuples

  ```
  CREATE FUNCTION sales_tax(subtotal real) RETURNS boolean AS $$
  DECLARE
     adjusted_subtotal real ;
  BEGIN
     adjusted_subtotal = subtotal * 0.06;
     BEGIN
         INSERT INTO temp VALUES (adjusted_subtotal) ;
         RETURN true ;
     EXCEPTION WHEN unique_violation THEN
         RETURN false ;
     END ;
  END ;
  $$ LANGUAGE plpgsql ;
  ```

- Now, when you run this function, a row is inserted into table temp.

### Executing queries

- When the query returns a single row, then we can read it directly
  into a variable.

- Note that when using variables as input/output, pl/pgsql does not
  need any special delimiters (be careful naming the variables so as
  not to clash with column names)

- Example:

  ```
    SELECT * INTO myrec FROM emp WHERE empname = myname;
    IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
    END IF;

  - input: myname, output: myrec
  ```

### Executing queries

- When the query returns multiple rows, then a loop is needed to go
  through them one by one.

  - A query returns a stream of tuples, which needs to be processed.
  - Equally important is closing the stream associated with a query
    if required by the programming language.

- Example:

  ```
  [ <<label>> ]
  FOR target IN query LOOP
  statements
  END LOOP [ label ];

  DECLARE
  myRow  RECORD ;
  lastX      INT ;
  yCnt       INT ;
  BEGIN
     lastX = 0 ;
     yCnt = 0 ;
     FOR myRow IN
            SELECT x,y, count(*) as num
            FROM temp GROUP BY x,y ORDER BY x, num ASC LOOP
        yCnt = yCnt + 1;
        IF yCnt < 4 AND lastX = myRow.x THEN
            INSERT INTO temp2 VALUES(myRow.x, myRow.y, myRow.num) ;
        ELSIF lastX <> myRow.x THEN
            lastX = myRow.x ;
            yCnt = 1 ;
            INSERT INTO temp2 VALUES(myRow.x, myRow.y, myRow.num) ;
        END IF ;
     END LOOP ;
  RETURN 1 ;
  END ;
  ```

- Note that procedure proc2() computes and inserts the top 3 y values
  (by count) for each x. Call it as:

  ```
  SELECT proc2() ;
  ```

- Here is an example of function that returns a table. Note that the
  table structure has to be defined in advance.

  ```
  CREATE TABLE names (name VARCHAR(255)) ;

  CREATE FUNCTION allnames() RETURNS SETOF names AS $$
  DECLARE
     row RECORD ;
  BEGIN
     FOR row in SELECT DISTINCT crsname FROM courses LOOP
         RETURN NEXT row ;
     END LOOP ;
     RETURN ;
  END ;
  $$ LANGUAGE plpgsql ;
  ```

- Since this function returns a table, we call it in the FROM clause:

  ```
  university=> select * from allnames();
            name
  ------------------------
  Spellcasting
  Practical Applications
  Mazes
  Alternate Dimensions
  Minor Mendings
  Transmogrification
  Advanced Spellcasting
  (7 rows)
  ```

- You can also use the tables returned by functions in queries:

  ::

  : university=> select * from allnames() a, courses c where a.name=c.crsname;

### Cursors

- A cursor is a query with a handle. Cursors may have input.

- Cursors may be defined once, and used many times to read tuples.

  A query in a cursor is optimized once, reducing the cost of
  optimizing the query many times.

- Functions may return reference to a cursor, allowing a program to
  read tuples that are returned.

- Cursors provide a more efficient implementation of queries returning
  many tuples.

- First, declare cursors:

  ```
  DECLARE curs2 CURSOR FOR SELECT * FROM tenk1;
  ```

- Then, execute the associated query by opening them:

  ```
  OPEN curs2;
  ```

- Then, retrieve tuples in the result using fetch:

  ```
  FETCH curs2 INTO foo, bar, baz;
  ```

or

  ```
  FOR recordvar IN curs2 LOOP
  ```

- When finished, close the cursor to release allocated memory:

  ```
  CLOSE curs2;
  ```

- Cursors can also be used for update/delete if it is pointing to a
  specific tuple (similar to the notion of an updatable view).


### Exceptions

- When an SQL statement is executed, if it is not successful, it raises an error. This error can be caught in the usual try/catch format:

  ```
  BEGIN
  statement
  EXCEPTION WHEN condition THEN
    statement
  END ;
  ```

- The exception conditions define integrity violations, statement
  errors, connection errors, etc.

- The pl/pgsql statements can also raise exceptions to be caught by
  the calling statement:

  ```
  RAISE NOTICE  ''
  RAISE EXCEPTION ''
  ```

- Also uncaught exceptions within the function will be raised when the
  function fails.