How to write PL/pgSQL functions (for PostgreSQL 8.3)

Introduction

This is an introduction to writing basic functions in PL/pgSQL. In the PostgreSQL world, the term "function" is synonymous with the term "Stored Procedure" in MS SQL Server. This article is useful for anyone who is very new to PostgreSQL and to writing basic functions in PL/pgSQL.

This article draws heavily on the book Practical PostgreSQL published under the Open Publication license.

Background

What is PL/pgSQL?

For a good definition, see http://en.wikipedia.org/wiki/PL/pgSQL. You will notice that in PostgreSQL, there are other languages you can use to write functions. Those are outside the scope of this article.

Key Concepts

Code Blocks

The function's main code block starts with a declaration section (DECLARE keyword).

The body code block starts with the BEGIN keyword and ends with the END keyword. The body block should return a value of its specified return type (RETURNS keyword).

Structure of a PL/pgSQL code block:

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    declaration;
    [...]
  BEGIN
    statement;
    [...]
    RETURN { variable_name | value }
  END;' LANGUAGE 'plpgsql';

Note: the return value "variable_name | value" must be the same type as type.

Syntax for simple variable declaration:

variable_name data_type [ := value ];

Syntax for more complicated variable declaration:

variable_name [ CONSTANT ] data_type [ NOT NULL ] [ { DEFAULT | := } value ];

Examples:

id INTEGER;
title VARCHAR(10);
price FLOAT;
six CONSTANT INTEGER := 6;
ten INTEGER NOT NULL := 10;

Comments

Single-line comments:

-- This will be interpreted as a single-line comment

Block comments:

/*
*  This is a
*  block
*  comment.
*/

Statements

Statements either assign a value to a variable or execute a query. Every statement should end with a semicolon character.

Expressions

Expressions are calculations or operations that return their results as one of PostgreSQL's base data types. An example expression is x := a + b, which adds the variables a and b, then assigns the result to the variable x.

Variables

Must be declared under the DECLARE keyword. If a variable is not initialized to a default value when it is declared, its value will default to the SQL NULL type.

Data types

Variables can be SQL data types or the RECORD data type

A brief list of commonly used data types in PL/pgSQL:

• boolean
• text
• char
• integer
• double precision
• date
• time

Assignment

Assignment operator (:=)

-- value of the right side is assigned to the left variable. 
left_variable := right_variable; 
left_variable := expression;

SELECT INTO: Primarily used to assign query results to variables declared as %ROWTYPE or RECORD types.

-- SYNTAX
SELECT INTO target_variable [, ...] target_column [, ...] select_clauses;

- Using the SELECT INTO statement

  DECLARE
    customer_fname varchar(100); 
    customer_lname varchar(100); 
  BEGIN
    SELECT INTO customer_fname, customer_lname
                first_name, last_name 
           FROM customers;
    IF NOT FOUND THEN
      return -1;
    END IF;
    return 1;
  END;

FOUND, IS NULL, and ISNULL should be used within a conditional (IF/THEN) statement.

Argument Variables

Example function calls from PSQL:

--this call:
SELECT get_author('John');
--works for this function:
create function get_author(fname varchar(100) returns integer AS

--this call:
SELECT get_author(1111);
--works for this function:
create function get_author(author_id integer) returns integer AS

Return Statements

The value of the return statement must match the "type" specified in the "RETURNS" clause of the function declaration.

Attributes

The %TYPE attribute is used to declare a variable to match the type of a database object.

variable_name table_name.column_name%TYPE

The %ROWTYPE attribute is used to match the row structure of a row.

DECLARE
  found_author authors%ROWTYPE;
BEGIN
  SELECT INTO found_author * FROM authors WHERE id = author_id;
END;

Conditional statements

The IF/THEN statement:

IF condition THEN 
  statement;
  [...]
END IF;

The IF/THEN/ELSE statement:

IF condition THEN 
  statement;
  [...]
ELSE
  statement;
  [...]
END IF;
END;

The IF/THEN/ELSE/IF statement:

IF condition THEN 
  statement;
  [...]
ELSE IF condition
  statement;
  [...]
END IF;

Loops

Unconditional loop:

[ <<label>> ]
  LOOP
    statement;
    [...]
    EXIT [ label ] [ WHEN condition ];
  END LOOP;

An unconditional loop statement will continue to loop until it reaches an EXIT statement.

A label identifies the loop block so you can specify which loop to exit when you have several loops nested inside each other.

The WHILE loop:

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

The FOR loop:

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

The FOR loop will perform a single iteration for each incremented value of identifier which is in the range of values between, and including, expression1 and expression2. The identifier value will be initialized to the value of expression1, regardless of any prior settings, and incremented by one each iteration. If REVERSE is specified, the identifier will be decremented rather than incremented.

Example of For loop:

DECLARE  
    row_data books%ROWTYPE;
BEGIN
  FOR i IN 0..15 LOOP
      FOR row_data IN SELECT * FROM books
        WHERE subject_id = i  LOOP       
        text_output := text_output || row_data.title || ''\n''; 
      END LOOP;
  END LOOP;        
  RETURN text_output;
END;

Handling Errors and Exceptions

The standard PostgreSQL error logging utility (elog) typically logs data to /var/log/messages, or to $PGDATA/serverlog, as well as displays to stderr.

A RAISE statement sends messages to elog.

RAISE level ''message string'' [, identifier [...] ];

• DEBUG: Will be ignored by a database running in production mode.
• NOTICE: Send in any PostgreSQL operation mode.
• EXCEPTION: Always send, and causes the current transaction to be aborted.

RAISE DEBUG ''The raise_test() function began.'';    
RAISE NOTICE ''Variable an_integer's value is now %.'',an_integer;
RAISE EXCEPTION ''Variable % changed. Transaction aborted.'',an_integer;

Calling Functions

The normal syntax to call another PL/pgSQL function from within PL/pgSQL is to either reference the function in a SQL SELECT statement, or during the assignment of a variable. For example:

SELECT function_identifier(arguments);
variable_identifier := function_identifier(arguments);
-- Use the PERFORM keyword to call a function and ignore its return data.
PERFORM function_identifier(arguments); 

Techniques for Returning Multiple Rows

One thing conveniently overlooked so far has been how to go about writing a function which will return multiple rows (a.k.a. recordset, resultset, table) and multiple tables. Here are a few methods:

Using "setof <database object type>"

CREATE OR REPLACE FUNCTION foo_func(prop_id_in integer,
 start_in date, end_in date) RETURNS SETOF foo_type 
  AS $$
DECLARE
  foo foo_type;
  occ RECORD;
BEGIN
  FOR occ IN
    SELECT x.*, y.*
      FROM 
        /*... sql query ...*/
      WHERE x.property_id = prop_id_in
        AND y.start_date BETWEEN start_in AND end_in
  LOOP
    foo.unit_id := occ.unit_id;
    /* ... fill foo  ... */
    RETURN NEXT foo;
  END LOOP;
  RETURN;  
END;
$$ LANGUAGE plpgsql STRICT;

Using a refcursor

create or replace function doit() returnqs refcursor as
$$
  declare
    r refcursor value 'result';
  begin
    /* some query that puts data in refcursor */
  end;
$$ language plpgsql;

-- from psql/app
begin;
select doit();
fetch all from result;
commit;

Using "setof text[]"

Using "returns setof record"

CREATE OR REPLACE FUNCTION sql_get_schema_full(select_table text) 
RETURNS SETOF RECORD AS $$
DECLARE
 sql_result record;
BEGIN
 FOR sql_result in EXECUTE<tt> '</tt>SELECT statement here' LOOP
   RETURN NEXT sql_result;
 END LOOP;
END;
$$

Calling the function:

SELECT * FROM sql_get_schema_full('temp_visit') 
AS temp_schema(table1 name, column1 name, constname name, 
  consttext text, table2 name, column2 name);

This article is a basic introduction to functions in PostgreSQL. There are many techniques to learn that are not addressed here, but hopefully will get you started.