PL/pgSQL Functions

User defined PL/pgSQL functions can be created to process the results of SQL queries.

See create_function() for further details. 

A PL/pgSQL function must have a body which consists of an optional declaration block followed by a mandatory procedural block.

Declaration Block

If the function requires variables other than the function arguments, these should be declared at the start of the function body within a declaration block that begins with a DECLARE and immediately precedes the procedural block. Each variable declaration in the block takes the form:

name [ CONSTANT ] type [ { DEFAULT | := | = } expression ];

Procedure Block

The procedural block is entirely enclosed within a BEGIN and END;

Each statement in the procedural block can be one of the following.

Return

A return statement returns from the function with the result of an expression

RETURN expression;

Assignment

An assignment statement assigns the value of an expression to a variable

variable { := | = } expression;

Query

A query statement executes a query and assigns the result to one or more variables

SELECT select_expressions INTO variables FROM ...;

IF

An IF statement evaluates a condition and executes one or more statements accordingly. The IF statement can optionally an use ELSE to execute alternative statements and IF statements can be daisy-chained with the use of ELSIF. 

IF <condition> 
THEN 
  <statements> 
END IF;

IF <condition> 
THEN 
  <statements> 
ELSE 
  <statements> 
END IF;

IF <condition> 
THEN 
  <statements> 
ELSIF <condition> 
THEN 
  <statements> 
ELSE 
  <statements> 
END IF;

CASE

A CASE statement evaluates an expression and executes one or more statements accordingly. An optional ELSE case executes if no other conditions were met.

CASE <search-expression>
    WHEN <expression> [, <expression> [ ... ]] THEN
      <statements>
  [ WHEN <expression> [, <expression> [ ... ]] THEN
      <statements>
    ... ]
  [ ELSE
      <statements> ]
END CASE;

LOOP

A simple LOOP statement repeatedly iterates statements until the loop is exited

LOOP
    <statements>
END LOOP;

EXIT

An EXIT statement terminates a loop and may optionally exit based on a condition.

EXIT [ WHEN <condition> ];

CONTINUE

A continue statement forces the current loop to immediately commence the next iteration and may optionally continue on a condition.

CONTINUE [ WHEN <condition> ];

WHILE

A WHILE loop iterates while a condition is true.

WHILE <condition> LOOP
    <statements>
END LOOP;

FOR

A FOR loop iterates a variable between two expressions, optionally by a step and optionally in reverse.

FOR <variable> IN [ REVERSE ] <expression> .. <expression> [ BY <expression> ] 
LOOP
    <statements>
END LOOP;

Examples

All of the following examples assume that the function has integer arguments X and Y.

-- return sum of X and Y
BEGIN
  RETURN X+Y;
END;

-- return name from mytable where id=X
DECLARE
  result TEXT;
BEGIN
  SELECT name INTO result FROM mytable WHERE id=X;
  RETURN result;
END;

-- return lowest fibonacci number greater than X + Y
DECLARE
  temp INTEGER;
  prev INTEGER;
  curr INTEGER;
BEGIN
  prev=1;
  curr=1;
  WHILE (prev+curr) < (X+Y) LOOP
    temp=prev;
    prev=curr;
    curr = temp+prev; 
  END LOOP;
  RETURN (prev+curr);
END;