RETURN with an expression
terminates the function and returns the value of expression to the caller. This form is
to be used for PL/pgSQL
functions that do not return a set.

When returning a scalar type, any expression can be used.
The expression's result will be automatically cast into the
function's return type as described for assignments. To
return a composite (row) value, you must write a record or
row variable as the expression.

The return value of a function cannot be left undefined.
If control reaches the end of the top-level block of the
function without hitting a RETURN
statement, a run-time error will occur.

If you have declared the function to return void, a RETURN statement
must still be provided; but in this case the expression
following RETURN is optional and
will be ignored if present.

When a PL/pgSQL function
is declared to return SETOF sometype, the procedure to follow
is slightly different. In that case, the individual items to
return are specified in RETURN NEXT
commands, and then a final RETURN
command with no argument is used to indicate that the
function has finished executing. RETURN
NEXT can be used with both scalar and composite data
types; in the latter case, an entire "table" of results will be returned.

Functions that use RETURN NEXT
should be called in the following fashion:

SELECT * FROM some_func();

That is, the function must be used as a table source in a
FROM clause.

RETURN NEXT does not actually
return from the function; it simply saves away the value of
the expression. Execution then continues with the next
statement in the PL/pgSQL
function. As successive RETURN NEXT
commands are executed, the result set is built up. A final
RETURN, which should have no
argument, causes control to exit the function.

Note: The current implementation of RETURN NEXT for PL/pgSQL stores the entire result
set before returning from the function, as discussed
above. That means that if a PL/pgSQL function produces a very
large result set, performance may be poor: data will be
written to disk to avoid memory exhaustion, but the
function itself will not return until the entire result
set has been generated. A future version of PL/pgSQL may allow users to define
set-returning functions that do not have this limitation.
Currently, the point at which data begins being written
to disk is controlled by the work_mem configuration variable.
Administrators who have sufficient memory to store larger
result sets in memory should consider increasing this
parameter.

When you use this form, you are actually nesting an
IF statement inside the ELSE part of an outer IF statement. Thus you need one END IF statement for each nested IF and one for the parent IF-ELSE. This is workable but grows tedious
when there are many alternatives to be checked. Hence the
next form.

IF-THEN-ELSIF-ELSE provides a
more convenient method of checking many alternatives in one
statement. Formally it is equivalent to nested IF-THEN-ELSE-IF-THEN commands, but only one
END IF is needed.

Here is an example:

IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- hmm, the only other possibility is that number is null
result := 'NULL';
END IF;

LOOP defines an unconditional
loop that is repeated indefinitely until terminated by an
EXIT or RETURN statement. The optional label can be
used by EXIT statements in nested
loops to specify which level of nesting should be
terminated.

If no label is given, the
innermost loop is terminated and the statement following
END LOOP is executed next. If
label is given, it must be
the label of the current or some outer level of nested loop
or block. Then the named loop or block is terminated and
control continues with the statement after the loop's/block's
corresponding END.

If WHEN is present, loop exit
occurs only if the specified condition is true, otherwise
control passes to the statement after EXIT.

EXIT can be used to cause early
exit from all types of loops; it is not limited to use with
unconditional loops.

Examples:

LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT; -- causes exit from the BEGIN block
END IF;
END;

This form of FOR creates a loop
that iterates over a range of integer values. The variable
name is automatically defined
as type integer and exists only inside
the loop. The two expressions giving the lower and upper
bound of the range are evaluated once when entering the loop.
The iteration step is normally 1, but is -1 when REVERSE is specified.

Some examples of integer FOR
loops:

FOR i IN 1..10 LOOP
-- some computations here
RAISE NOTICE 'i is %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- some computations here
END LOOP;

If the lower bound is greater than the upper bound (or
less than, in the REVERSE case), the
loop body is not executed at all. No error is raised.

If the loop is terminated by an EXIT statement, the last assigned row value is
still accessible after the loop.

The FOR-IN-EXECUTE statement is
another way to iterate over rows:

[<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP;

This is like the previous form, except that the source
SELECT statement is specified as a
string expression, which is evaluated and replanned on each
entry to the FOR loop. This allows the
programmer to choose the speed of a preplanned query or the
flexibility of a dynamic query, just as with a plain EXECUTE statement.

Note: The PL/pgSQL parser presently
distinguishes the two kinds of FOR
loops (integer or query result) by checking whether
.. appears outside any parentheses
between IN and LOOP. If .. is not
seen then the loop is presumed to be a loop over rows.
Mistyping the .. is thus likely to
lead to a complaint along the lines of "loop variable of loop over rows must be a record
or row variable", rather than the simple syntax
error one might expect to get.

By default, any error occurring in a PL/pgSQL function aborts execution of the
function, and indeed of the surrounding transaction as well.
You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause. The syntax is an extension of
the normal syntax for a BEGIN
block:

If no error occurs, this form of block simply executes all
the statements, and then
control passes to the next statement after END. But if an error occurs within the
statements, further processing
of the statements is abandoned,
and control passes to the EXCEPTION
list. The list is searched for the first condition matching the error that
occurred. If a match is found, the corresponding handler_statements are executed, and then
control passes to the next statement after END. If no match is found, the error propagates
out as though the EXCEPTION clause
were not there at all: the error can be caught by an enclosing
block with EXCEPTION, or if there is
none it aborts processing of the function.

The condition names can be
any of those shown in Appendix
A. A category name matches any error within its category.
The special condition name OTHERS
matches every error type except QUERY_CANCELED. (It is possible, but often
unwise, to trap QUERY_CANCELED by
name.) Condition names are not case-sensitive.

If a new error occurs within the selected handler_statements, it cannot be caught
by this EXCEPTION clause, but is
propagated out. A surrounding EXCEPTION clause could catch it.

When an error is caught by an EXCEPTION clause, the local variables of the
PL/pgSQL function remain as
they were when the error occurred, but all changes to
persistent database state within the block are rolled back. As
an example, consider this fragment:

When control reaches the assignment to y, it will fail with a division_by_zero error. This will be caught by
the EXCEPTION clause. The value
returned in the RETURN statement will
be the incremented value of x, but the
effects of the UPDATE command will
have been rolled back. The INSERT
command preceding the block is not rolled back, however, so the
end result is that the database contains Tom Jones not Joe
Jones.

Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than a block without one.
Therefore, don't use EXCEPTION
without need.

Example 35-1. Exceptions with UPDATE/INSERT

This example uses exception handling to perform either
UPDATE or INSERT, as appropriate.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.