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