PostgreSQL PL/pgSQL Notes

To see the first part of the notes on PL/pgSQL, see this daily reading article.

Control Structures

Control structures are probably the most useful (and important) part of PL/pgSQL. With PL/pgSQL's control structure, you can manipulate PostgreSQL data in a very flexible and powerful way.

RETURN

There are two commands available that allow you to return data from a function: RETURN and RETURN NEXT

RETURN expression;

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.

REURN NEXT and RETURN QUERY

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

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.

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() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

A procedure does not have to have a return value. A procedure can end with a RETURN statement.

Conditionals

IF and CASE statements let you execute alternative commands based on certain conditions. PL/pgSQL has three forms of IF and two forms of CASE:

1. IF ... THEN ... END IF
2. IF ... THEN ... ELSE ... END IF
3. IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

1. CASE ... WHEN ... THEN ... ELSE ... END CASE
2. CASE WHEN ... THEN ... ELSE ... END CASE

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.

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.

IF-THEN-ELSIF provides a convenient method for 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. If none of the IF conditions is true, then the ELSE block is executed.

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

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

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;

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

The simple form of CASE provides conditional execution based on equality of operands.

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

The simplest 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, the the corresponding statements are executed, and then control passes to the next statement after END CASE. If no match is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.

The searched form of CASE provides conditional execution based on truth 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.

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.

LOOP

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

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 with nested loops to specify which loop those statements refer to.

EXIT

EXIT [ label ] [ WHEN boolean-expression ]l

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.

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.

CONTINUE

CONTINUE [ label ] [ WHEN boolean-expression ];

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 WHEN is specified, the next iteration of the loop is begin 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.

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 ];

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.

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

FOR (Integer Variant)

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

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.

FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

If the lower bound is greater than the upper bound, the loop body is not executed at all. No error is raised.

LOOPING through Query Results

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

Using a different type of FOR loop, you can iterate through the results of a query and manipulate that data accordingly. The syntax is shown above. The target is a record, row variable, or comma-separated list of scalar values. The target is successively assigned each row resulting form the query loop and the loop body is executed for each row.

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Now "mviews" has one record with information about the materialized view

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

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. Without SLICE, or if SLICE 0 is specified, the loop iterates through individual elements of the array produced by evaluating expression.

[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP [ label ];

Trapping Arrays

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

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

Cursors

Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows. A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return large row sets from functions.

Declaring Cursor Variables

All access to cursors goes through cursor variables, which are always of the special data type refcursor. One way to create a cursor variable is just to declare it as a variable of type refcursor. Another way is to use the cursor declaration syntax:

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

If SCROLL is specified, the cursor will be capable of scrolling backward; if NO SCROLL is specified, backward fetches will be rejected; if nether specification appears, it is query dependent whether backward fetches will be allowed. arguments, if specified, is a comma-separated list of pairs name datatype that define names to be replaced by parameter values in the given query.

DECLARE
    curs1 refcursor; -- can be used with any query
    curs2 CURSOR FOR SELECT * FROM tenk1; -- has a specified query bound to it
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key; -- parameterized query bound to it

Operating Cursors

Before a cursor can be used to retrieve rows, it must be opened. PL/pgSQL has three forms of OPEN statements, two of which can unbound cursor variables while the third uses a bound cursor variable.

Opening a cursor involves creating a server-internal data structure called a portal, which holds the execution state for the cursor's query. A portal has a name, which must be unique within the session for the duration of the portal's existence.

I will come back to cursors when I have a clear use case for them.

Transaction management

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command.

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

A new transaction starts out with default transaction characteristics such as transaction isolation level. In cases where transactions are committed in a loop, it might be desirable to start new transactions automatically with the same characteristics as the previous one. The commands COMMIT AND CHAIN and ROLLBACK AND CHAIN accomplish this.

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command.

PL/pgSQL does not support savepoints (SAVEPOINT/ROLLBACK TO SAVEPOINT/RELEASE SAVEPOINT commands). Typical usage patterns for savepoints can be replaced by blocks with exception handlers.

Errors and Messages

Us ethe RAISE statement to report messages and raise errors.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

The level option specifies the error severity. Allowed levels are:

• DEBUG
• LOG
• INFO
• NOTICE
• WARNING
• EXCEPTION
• • Default level
  • This raises an error; the other levels only generate messages of different priority levels.

How the messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_mesages and client_min_messages configuration variables.

After level, you can specify a format string (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message.

-- The % is replaced by v_job_id
-- Use %% to escape %
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

You can attach additional information to the error report by writing USING followed by option=expression items. Each expression can e any string-valued expression. The allowed option key words are:

• MESSAGE
• • Sets the error message text. This option can't be used in the form of raise that includes a format string before USING
• DETAIL
• • Supplies an error detail message
• HINT
• • Supplies a hint message
• ERRCODE
• COLUMN
• DATATYPE
• TABLE
• SCHEMA
• • Supplies the name of the related object

The ASSERT statement is a convenient shorthand for inserting debugging checks into PL/pgSQL functions.

ASSERT condition [ , message ];

The condition is a Boolean expression that is expected to always evaluate to true; if it does, the ASSERT statement does nothing further. If the result is false or null, then an ASSERT_FAILURE exception is raised.

If the optional message is provided, it is an expression whose result (if not null) replaces the default error message text assertion failed, should the condition fail. The message expression is not evaluated in the normal case where assertion succeeds.

Trigger Functions

See this daily reading article to read about trigger functions.