43.6. Control Structures
Control structures are probably the most useful (and important) part of PL/pgSQL. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very flexible and powerful way.
43.6.1. Returning from a Function
There are two commands available that allow you to return data from a function: RETURN
and RETURN NEXT
.
43.6.1.1. RETURN
RETURN
RETURN
with an expression terminates the function and returns the value of expression
to the caller. This form is used for PL/pgSQL functions that do not return a set.
In a function that returns a scalar type, the expression's result will automatically be cast into the function's return type as described for assignments. But to return a composite (row) value, you must write an expression delivering exactly the requested column set. This may require use of explicit casting.
If you declared the function with output parameters, write just RETURN
with no expression. The current values of the output parameter variables will be returned.
If you declared the function to return void
, a RETURN
statement can be used to exit the function early; but do not write an expression following RETURN
.
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. This restriction does not apply to functions with output parameters and functions returning void
, however. In those cases a RETURN
statement is automatically executed if the top-level block finishes.
Some examples:
43.6.1.2. RETURN NEXT
And RETURN QUERY
RETURN NEXT
And RETURN QUERY
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 by a sequence of RETURN NEXT
or RETURN QUERY
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; with a composite result type, an entire “table” of results will be returned. RETURN QUERY
appends the results of executing a query to the function's result set. RETURN NEXT
and RETURN QUERY
can be freely intermixed in a single set-returning function, in which case their results will be concatenated.
RETURN NEXT
and RETURN QUERY
do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the PL/pgSQL function. As successive RETURN NEXT
or RETURN QUERY
commands are executed, the result set is built up. A final RETURN
, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).
RETURN QUERY
has a variant RETURN QUERY EXECUTE
, which specifies the query to be executed dynamically. Parameter expressions can be inserted into the computed query string via USING
, in just the same way as in the EXECUTE
command.
If you declared the function with output parameters, write just RETURN NEXT
with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returning SETOF record
when there are multiple output parameters, or SETOF
sometype
when there is just one output parameter of type sometype
, in order to create a set-returning function with output parameters.
Here is an example of a function using RETURN NEXT
:
Here is an example of a function using RETURN QUERY
:
Note
The current implementation of RETURN NEXT
and RETURN QUERY
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 might 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 might 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.
43.6.2. Returning from a Procedure
A procedure does not have a return value. A procedure can therefore end without a RETURN
statement. If you wish to use a RETURN
statement to exit the code early, write just RETURN
with no expression.
If the procedure has output parameters, the final values of the output parameter variables will be returned to the caller.
43.6.3. Calling a Procedure
A PL/pgSQL function, procedure, or DO
block can call a procedure using CALL
. Output parameters are handled differently from the way that CALL
works in plain SQL. Each OUT
or INOUT
parameter of the procedure must correspond to a variable in the CALL
statement, and whatever the procedure returns is assigned back to that variable after it returns. For example:
The variable corresponding to an output parameter can be a simple variable or a field of a composite-type variable. Currently, it cannot be an element of an array.
43.6.4. Conditionals
IF
and CASE
statements let you execute alternative commands based on certain conditions. PL/pgSQL has three forms of IF
:
IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
and two forms of CASE
:
CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE
43.6.4.1. IF-THEN
IF-THEN
IF-THEN
statements are the simplest form of IF
. The statements between THEN
and END IF
will be executed if the condition is true. Otherwise, they are skipped.
Example:
43.6.4.2. IF-THEN-ELSE
IF-THEN-ELSE
IF-THEN-ELSE
statements add to IF-THEN
by letting you specify an alternative set of statements that should be executed if the condition is not true. (Note this includes the case where the condition evaluates to NULL.)
Examples:
43.6.4.3. IF-THEN-ELSIF
IF-THEN-ELSIF
Sometimes there are more than just two alternatives. IF-THEN-ELSIF
provides a convenient method of checking several alternatives in turn. The IF
conditions are tested successively until the first one that is true is found. Then the associated statement(s) are executed, after which control passes to the next statement after END IF
. (Any subsequent IF
conditions are not tested.) If none of the IF
conditions is true, then the ELSE
block (if any) is executed.
Here is an example:
The key word ELSIF
can also be spelled ELSEIF
.
An alternative way of accomplishing the same task is to nest IF-THEN-ELSE
statements, as in the following example:
However, this method requires writing a matching END IF
for each IF
, so it is much more cumbersome than using ELSIF
when there are many alternatives.
43.6.4.4. Simple CASE
CASE
The simple form of CASE
provides conditional execution based on equality of operands. The search-expression
is evaluated (once) and successively compared to each expression
in the WHEN
clauses. If a match is found, then the corresponding statements
are executed, and then control passes to the next statement after END CASE
. (Subsequent WHEN
expressions are not evaluated.) If no match is found, the ELSE
statements
are executed; but if ELSE
is not present, then a CASE_NOT_FOUND
exception is raised.
Here is a simple example:
43.6.4.5. Searched CASE
CASE
The searched form of CASE
provides conditional execution based on truth of Boolean expressions. Each WHEN
clause's boolean-expression
is evaluated in turn, until one is found that yields true
. Then the corresponding statements
are executed, and then control passes to the next statement after END CASE
. (Subsequent WHEN
expressions are not evaluated.) If no true result is found, the ELSE
statements
are executed; but if ELSE
is not present, then a CASE_NOT_FOUND
exception is raised.
Here is an example:
This form of CASE
is entirely equivalent to IF-THEN-ELSIF
, except for the rule that reaching an omitted ELSE
clause results in an error rather than doing nothing.
43.6.5. Simple Loops
With the LOOP
, EXIT
, CONTINUE
, WHILE
, FOR
, and FOREACH
statements, you can arrange for your PL/pgSQL function to repeat a series of commands.
43.6.5.1. LOOP
LOOP
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
and CONTINUE
statements within nested loops to specify which loop those statements refer to.
43.6.5.2. EXIT
EXIT
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 specified, the loop exit occurs only if boolean-expression
is true. Otherwise, control passes to the statement after EXIT
.
EXIT
can be used with all types of loops; it is not limited to use with unconditional loops.
When used with a BEGIN
block, EXIT
passes control to the next statement after the end of the block. Note that a label must be used for this purpose; an unlabeled EXIT
is never considered to match a BEGIN
block. (This is a change from pre-8.4 releases of PostgreSQL, which would allow an unlabeled EXIT
to match a BEGIN
block.)
Examples:
43.6.5.3. CONTINUE
CONTINUE
If no label
is given, the next iteration of the innermost loop is begun. That is, all statements remaining in the loop body are skipped, and control returns to the loop control expression (if any) to determine whether another loop iteration is needed. If label
is present, it specifies the label of the loop whose execution will be continued.
If WHEN
is specified, the next iteration of the loop is begun only if boolean-expression
is true. Otherwise, control passes to the statement after CONTINUE
.
CONTINUE
can be used with all types of loops; it is not limited to use with unconditional loops.
Examples:
43.6.5.4. WHILE
WHILE
The WHILE
statement repeats a sequence of statements so long as the boolean-expression
evaluates to true. The expression is checked just before each entry to the loop body.
For example:
43.6.5.5. FOR
(Integer Variant)
FOR
(Integer Variant)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 (any existing definition of the variable name is ignored within the loop). The two expressions giving the lower and upper bound of the range are evaluated once when entering the loop. If the BY
clause isn't specified the iteration step is 1, otherwise it's the value specified in the BY
clause, which again is evaluated once on loop entry. If REVERSE
is specified then the step value is subtracted, rather than added, after each iteration.
Some examples of integer FOR
loops:
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 a label
is attached to the FOR
loop then the integer loop variable can be referenced with a qualified name, using that label
.
43.6.6. Looping through Query Results
Using a different type of FOR
loop, you can iterate through the results of a query and manipulate that data accordingly. The syntax is:
The target
is a record variable, row variable, or comma-separated list of scalar variables. The target
is successively assigned each row resulting from the query
and the loop body is executed for each row. Here is an example:
If the loop is terminated by an EXIT
statement, the last assigned row value is still accessible after the loop.
The query
used in this type of FOR
statement can be any SQL command that returns rows to the caller: SELECT
is the most common case, but you can also use INSERT
, UPDATE
, or DELETE
with a RETURNING
clause. Some utility commands such as EXPLAIN
will work too.
PL/pgSQL variables are replaced by query parameters, and the query plan is cached for possible re-use, as discussed in detail in Section 43.11.1 and Section 43.11.2.
The FOR-IN-EXECUTE
statement is another way to iterate over rows:
This is like the previous form, except that the source query 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. As with EXECUTE
, parameter values can be inserted into the dynamic command via USING
.
Another way to specify the query whose results should be iterated through is to declare it as a cursor. This is described in Section 43.7.4.
43.6.7. Looping through Arrays
The FOREACH
loop is much like a FOR
loop, but instead of iterating through the rows returned by an SQL query, it iterates through the elements of an array value. (In general, FOREACH
is meant for looping through components of a composite-valued expression; variants for looping through composites besides arrays may be added in future.) The FOREACH
statement to loop over an array is:
Without SLICE
, or if SLICE 0
is specified, the loop iterates through individual elements of the array produced by evaluating the expression
. The target
variable is assigned each element value in sequence, and the loop body is executed for each element. Here is an example of looping through the elements of an integer array:
The elements are visited in storage order, regardless of the number of array dimensions. Although the target
is usually just a single variable, it can be a list of variables when looping through an array of composite values (records). In that case, for each array element, the variables are assigned from successive columns of the composite value.
With a positive SLICE
value, FOREACH
iterates through slices of the array rather than single elements. The SLICE
value must be an integer constant not larger than the number of dimensions of the array. The target
variable must be an array, and it receives successive slices of the array value, where each slice is of the number of dimensions specified by SLICE
. Here is an example of iterating through one-dimensional slices:
43.6.8. Trapping Errors
By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction. 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
and ASSERT_FAILURE
. (It is possible, but often unwise, to trap those two error types by name.) Condition names are not case-sensitive. Also, an error condition can be specified by SQLSTATE
code; for example these are equivalent:
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 43.2. Exceptions with UPDATE
/INSERT
This example uses exception handling to perform either UPDATE
or INSERT
, as appropriate. It is recommended that applications use INSERT
with ON CONFLICT DO UPDATE
rather than actually using this pattern. This example serves primarily to illustrate use of PL/pgSQL control flow structures:
This coding assumes the unique_violation
error is caused by the INSERT
, and not by, say, an INSERT
in a trigger function on the table. It might also misbehave if there is more than one unique index on the table, since it will retry the operation regardless of which index caused the error. More safety could be had by using the features discussed next to check that the trapped error was the one expected.
43.6.8.1. Obtaining Information About An Error
Exception handlers frequently need to identify the specific error that occurred. There are two ways to get information about the current exception in PL/pgSQL: special variables and the GET STACKED DIAGNOSTICS
command.
Within an exception handler, the special variable SQLSTATE
contains the error code that corresponds to the exception that was raised (refer to Table A.1 for a list of possible error codes). The special variable SQLERRM
contains the error message associated with the exception. These variables are undefined outside exception handlers.
Within an exception handler, one may also retrieve information about the current exception by using the GET STACKED DIAGNOSTICS
command, which has the form:
Each item
is a key word identifying a status value to be assigned to the specified variable
(which should be of the right data type to receive it). The currently available status items are shown in Table 43.2.
Table 43.2. Error Diagnostics Items
Name | Type | Description |
---|---|---|
|
| the SQLSTATE error code of the exception |
|
| the name of the column related to exception |
|
| the name of the constraint related to exception |
|
| the name of the data type related to exception |
|
| the text of the exception's primary message |
|
| the name of the table related to exception |
|
| the name of the schema related to exception |
|
| the text of the exception's detail message, if any |
|
| the text of the exception's hint message, if any |
|
| line(s) of text describing the call stack at the time of the exception (see Section 43.6.9) |
If the exception did not set a value for an item, an empty string will be returned.
Here is an example:
43.6.9. Obtaining Execution Location Information
The GET DIAGNOSTICS
command, previously described in Section 43.5.5, retrieves information about current execution state (whereas the GET STACKED DIAGNOSTICS
command discussed above reports information about the execution state as of a previous error). Its PG_CONTEXT
status item is useful for identifying the current execution location. PG_CONTEXT
returns a text string with line(s) of text describing the call stack. The first line refers to the current function and currently executing GET DIAGNOSTICS
command. The se