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:

Statements that return no output but a status (successful or not, and what was wrong)
Statements that return a single tuple
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;
```

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.

SQL - Procedural Programming with PL/PGSQL

Contents

SQL - Procedural Programming with PL/PGSQL#

Overview of pl/pgsql#

Programming constructs#

Handling SQL:#

Executing queries#

Executing queries#

Cursors#

Exceptions#