Control structures

RETURN

RETURN expression;

RETURN expression terminates the function and passes the expression value to the caller. Use this form in functions that do not return a set.

• Scalar return type: The expression is automatically converted to the declared return type.

• Composite (row) return type: Write an expression that produces exactly the required columns. Explicit casting may be required.

• Output parameters: Write RETURN without an expression. The current values of the output parameter variables are returned.

• `void` return type: Write RETURN without an expression to exit the function early.

A function must reach a RETURN statement before control exits its top-level block, or a runtime error occurs. Functions with output parameters and functions returning void are exempt: a RETURN is executed automatically when the top-level block ends.

-- Scalar return
RETURN 1 + 2;
RETURN scalar_var;

-- Composite return (cast columns to the correct types)
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);

RETURN NEXT and RETURN QUERY

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

Use these statements in functions declared to return SETOF sometype. Instead of returning immediately, each RETURN NEXT or RETURN QUERY appends rows to the result set. A final RETURN with no arguments signals that the function is done.

• RETURN NEXT works for scalar and composite data types. For composite types, a complete table of results is returned.

• RETURN QUERY appends the output of a query to the result set.

• Both statements can be mixed freely in a single function; their results are concatenated.

• RETURN QUERY EXECUTE dynamically specifies the query. Insert parameter values with USING, the same way as in the EXECUTE command.

For functions with output parameters, write RETURN NEXT without an expression. Each invocation saves the current output parameter values as one row of the result. To return a set with multiple output parameters, declare the function as RETURN SETOF record. For a single output parameter of type sometype, declare it as RETURN SETOF sometype.

The following example uses RETURN NEXT to return all rows from a table:

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURN SETOF foo IS
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- Some processing can be done here.
        RETURN NEXT r; -- Return the current row of the SELECT statement.
    END LOOP;
    RETURN;
END;

SELECT * FROM get_all_foo();

The following example uses RETURN QUERY and raises an exception when no rows are found:

CREATE FUNCTION get_available_flightid(date) RETURN SETOF integer IS
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- Check whether rows are returned.
    -- If no rows are returned, throw an exception.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
END;

-- Return available flights, or throw an exception if no flights are available.
SELECT * FROM get_available_flightid(CURRENT_DATE);

The current implementations of RETURN NEXT and RETURN QUERY accumulate the entire result set in memory before the function returns. For large result sets, this can cause poor performance: data is written to disk to prevent memory exhaustion, but the function does not return until the entire result set is generated. The point at which data starts being written to disk is controlled by the work_mem configuration variable. If you have enough memory to store large result sets, consider increasing work_mem.

IF-THEN

IF boolean-expression THEN
    statements
END IF;

Executes statements only when the condition is true; otherwise skips them.

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

Executes the first block when the condition is true, and the ELSE block when the condition is false or NULL.

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

IF-THEN-ELSIF

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
  ...
]
[ ELSE
    statements ]
END IF;

Checks conditions in order and executes the block for the first condition that is true, then skips the rest. If no condition is true, the ELSE block (if present) executes.

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

ELSIF can also be spelled ELSEIF.

IF-THEN-ELSIF is cleaner than nested IF-THEN-ELSE when checking multiple conditions. Each nested IF requires a matching END IF, making the code harder to maintain:

-- Equivalent nested IF approach (less readable)
IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

Simple CASE

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

Evaluates search-expression once and compares it to each WHEN expression in order. Executes the matching block, then passes control to the statement after END CASE. If no match is found and there is no ELSE clause, a CASE_NOT_FOUND exception is thrown.

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

Searched CASE

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Evaluates each WHEN boolean expression in order and executes the block for the first one that is true. If no expression is true and there is no ELSE clause, a CASE_NOT_FOUND exception is thrown. This form is equivalent to IF-THEN-ELSIF, with the key difference that omitting ELSE raises an exception rather than doing nothing.

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

LOOP

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

An unconditional loop that repeats indefinitely until terminated by EXIT or RETURN. Attach a label to let EXIT and CONTINUE in nested loops target a specific level.

EXIT

EXIT [ label ] [ WHEN boolean-expression ];

• Without a label: exits the innermost loop and continues after END LOOP.

• With a label: exits the named loop or block and continues after its corresponding END.

• With WHEN: exits only when the boolean expression is true.

EXIT works in all loop types, including unconditional loops.

When used with a BEGIN block, EXIT requires a label. In recent PostgreSQL versions, an unlabeled EXIT does not match a BEGIN block (support for this was dropped after PostgreSQL 8.4).

LOOP
    -- Some computations
    IF count > 0 THEN
        EXIT; -- Exit the loop.
    END IF;
END LOOP;

LOOP
    -- Some computations
    EXIT WHEN count > 0; -- Same result as the previous example
END LOOP;

<<ablock>>
BEGIN
    -- Some computations
    IF stocks > 100000 THEN
        EXIT ablock;  -- Causes an exit from the BEGIN block
    END IF;
    -- If stocks > 100000, the computations here are skipped.
END;

CONTINUE

CONTINUE [ label ] [ WHEN boolean-expression ];

• Without a label: skips the remaining loop body and starts the next iteration of the innermost loop.

• With a label: starts the next iteration of the specified loop.

• With WHEN: continues only when the boolean expression is true.

CONTINUE works in all loop types, including unconditional loops.

LOOP
    -- Some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- Some computations for count IN [50 .. 100]
END LOOP;

WHILE

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

Repeats the loop body as long as the boolean expression is true. The expression is evaluated before each iteration.

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- Some computations
END LOOP;

WHILE NOT done LOOP
    -- Some computations
END LOOP;

FOR (integer variant)

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

Iterates over a range of integers. The loop variable name is automatically typed as integer and exists only within the loop; any existing variable with the same name is hidden. Both bound expressions are evaluated once when the loop starts. The default step is 1; use BY to specify a different step. With REVERSE, the step is subtracted rather than added each iteration.

If the lower bound is greater than the upper bound (or less than in REVERSE), the loop body is not executed and no error is thrown.

FOR i IN 1..10 LOOP
    -- Iterates over 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- Iterates over 10, 9, 8, 7, 6, 5, 4, 3, 2, 1
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- Iterates over 10, 8, 6, 4, 2
END LOOP;

If a label is attached, the loop variable can be referenced with a qualified name using that label.

FOR-IN-EXECUTE (dynamic query loop)

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

Similar to the query loop above, but the source query is a string expression that is evaluated and replanned each time the loop is entered. This gives you the flexibility of a dynamic command while still allowing parameter injection via USING. As an alternative, declare the query as a cursor.

Performance considerations

Entering and exiting a block with an EXCEPTION clause is significantly more expensive than a plain block. Use EXCEPTION only when you need it.

UPDATE and INSERT with error handling

The following example uses exception handling to implement an upsert: try UPDATE first, fall back to INSERT if the row does not exist, and retry if a concurrent insert causes a unique key conflict.

For new code, use INSERT ... ON CONFLICT DO UPDATE instead of this pattern. The example below is intended to illustrate PL/SQL control flow.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURN VOID IS
BEGIN
    LOOP
        -- First, try to update the key.
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- The key does not exist. Try to insert it.
        -- A concurrent insert of the same key may cause a unique key failure.
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing and loop to try the UPDATE again.
        END;
    END LOOP;
END;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

This code assumes the unique_violation error comes from the INSERT, not from a trigger. If the table has more than one unique index, the loop retries regardless of which index caused the error. Use the diagnostics features below to verify that the caught error is the one you expect.

Get information about an error

Within an exception handler, two special variables are available:

• SQLSTATE: the error code of the current exception

• SQLERRM: the error message of the current exception

Both variables are undefined outside the handler.

For more detail, use GET STACKED DIAGNOSTICS:

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

Each item is a keyword identifying a status value, assigned to variable of the appropriate data type. Available items:

If a diagnostics item has no value, an empty string is returned.

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- Some processing that might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;