Practical PostgreSQL

Controlling Program Flow

Most programming languages in existence provide ways of controlling the flow of programs they are used to create. PL/pgSQL is no different. Technically, by defining the structure of statements within a PL/pgSQL function, you are controlling its “flow,” in that you are controlling the manner in which it operates and the order its operations are executed. However, there are more extensive ways in which you can control the flow of a PL/pgSQL, such as conditional statements and the use of loops.

Conditional Statements

A conditional statement specifies an action (or set of actions) that should be executed instead of continuing execution of the function, based on the result of logical condition specified within the statement. That definition of conditional statements may make them sound a bit complex, but they are actually fairly simple. Essentially, a conditional statement informs the parser that if a given condition is true, a specified action should be taken.

The IF/THEN statement

The IF/THEN statement allows you to specify a statement (or block of statements) that should be executed if a given condition evaluates true. The syntax of the IF/THEN statement is shown in Example 11-34.

Example 11-34. Syntax of an IF/THEN statement

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    declarations
  BEGIN

    IF condition THEN
      statement;
      [...]
    END IF;

  END;
' LANGUAGE 'plpgsql';

In Example 11-35, a function is created that checks the stock of a book when given ...