Controlling Program Flow

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.

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 its book ID and edition number. The book ID is an internally recorded and tracked number listed in a few of the database's tables; thus, this function is designed to be used by other functions, as most users won't directly know the book ID number. The stock_amount function first retrieves the book's ISBN number with a SELECT INTO statement.

If the SELECT INTO statement could not retrieve an ISBN number for the book with the provided book ID number and edition number the stock amount function returns a value of –1, which should be interpreted as an error by the function that called it. The function's flow continues on if there was an ISBN number found for the book, and another SELECT INTO statement is used to retrieve the amount of stock remaining for the book in question. The stock amount is then returned and the function ends.

CREATE FUNCTION stock_amount (integer, integer) RETURNS integer AS '
  DECLARE
     
     -- Declare aliases for function arguments.
    b_id ALIAS FOR $1;
    b_edition ALIAS FOR $2;
     
     -- Declare variable to store the ISBN number.
    b_isbn TEXT;
     
     -- Declare variable to store the stock amount.
    stock_amount INTEGER;
  
  BEGIN
     
     -- This SELECT INTO statement retrieves the ISBN number of the row in
     -- the editions table that had both the book ID number and edition number
     -- that were provided as function arguments.
    SELECT INTO b_isbn isbn FROM editions WHERE
      book_id = b_id AND edition = b_edition;
     
     -- Check to see if the ISBN number retrieved is NULL.  This will
     -- happen if there is not an existing book with both the ID number
     -- and edition number specified in the function arguments. If the
     -- ISBN is null, the function returns a value of -1 and ends.
    IF b_isbn IS NULL THEN
      RETURN -1;
    END IF;
     
     -- Retrieve the amount of books available from the stock table
     -- and record the number in the stock_amount variable.
    SELECT INTO stock_amount stock FROM stock WHERE isbn = b_isbn;
     
     -- Return the amount of books available.
    RETURN stock_amount;
  
  END;
' LANGUAGE 'plpgsql';

Example 11-36 shows the result of the stock_amount function when it is called for the book ID value 7808 and edition number 1.

booktown=# SELECT stock_amount(7808,1);
 stock_amount
--------------
           22
(1 row)

The IF/THEN/ELSE statement

The IF/THEN/ELSE statement allows you to specify a block of statements that should be executed if a condition evaluates to true, and also a block of statements that should be executed if the condition evaluates to false. The syntax of the IF/THEN/ELSE statement is shown in Example 11-37.

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    declarations
  BEGIN
    
    IF condition THEN
      statement;
      [...]
    ELSE
      statement;
      [...]
    END IF;
  END;
' LANGUAGE 'plpgsql';

In Example 11-38, essentially the same steps that were taken in Example 11-35 are taken again to retrieve the ISBN number, store it, then use it to retrieve the quantity in stock for the book in question.

Once the in-stock number is retrieved, an IF/THEN/ELSE statement is used to decide whether or not the number is above zero. If it is above zero the function returns a TRUE value, indicating that the title is in stock. If the in-stock is below zero, the function returns a FALSE value, indicating the title is out of stock. Again, this is a function designed to be used by another function, so only values are returned. Returned values must be interpreted by the function that called the in_stock() function.

CREATE FUNCTION in_stock (integer,integer) RETURNS boolean AS '
  DECLARE
     
     -- Declare aliases for function arguments.
    b_id ALIAS FOR $1;
    b_edition ALIAS FOR $2;
     
     -- Declare a text variable to hold the ISBN of the book
     -- once found.
    b_isbn TEXT;
     
     -- Declare an integer variable to hold the amount of stock.
    stock_amount INTEGER;
  
  BEGIN
     
     -- This SELECT INTO statement retrieves the ISBN number of
     -- the row in the editions table that had both the book ID
     -- number and edition number that were provided as function
     -- arguments.
    SELECT INTO b_isbn isbn FROM editions WHERE
      book_id = b_id AND edition = b_edition;
     
     -- Check to see if the ISBN number retrieved  is NULL.  This
     -- will happen if there is not an existing book with both the
     -- ID number and edition number specified in the function
     -- arguments. If the ISBN is null, the function returns a
     -- FALSE value and ends.
    IF b_isbn IS NULL THEN
      RETURN FALSE;
    END IF;
     
     -- Retrieve the amount of books available from the stock
     -- table and record the number in the stock_amount variable.
    SELECT INTO stock_amount stock FROM stock WHERE isbn = b_isbn;
     
     -- Use an IF/THEN/ELSE check to see if the amount of books
     -- available is less than or equal to 0.  If so, return FALSE.
     -- If not, return TRUE.
    IF stock_amount <= 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  
  END;
' LANGUAGE 'plpgsql';

Example 11-39 shows the result of the check_stock() function when it is called with the book ID value 4513 and edition number 2. A value of true is returned, indicating that the title is in stock.

booktown=# SELECT in_stock(4513,2);
 in_stock
----------
 t
(1 row)

Example 11-39 shows that a TRUE value was returned, indicating that the title is in stock.

The IF/THEN/ELSE/IF statement

The IF/THEN/ELSE/IF statement is a mechanism for linking several IF statements together in a series. First, one condition is checked. If the first condition evaluates to FALSE, another condition is checked, and so forth. A final ELSE can provide for the case when no condition evaluates to TRUE. The syntax for the IF/THEN/ELSE/IF statement follows:

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    declarations
  BEGIN
    IF condition THEN
      statement;
      [...]
    ELSE IF condition
      statement;
      [...]
    END IF;
  END;
' LANGUAGE 'plpgsql';

This syntax shows the creation of a function that demonstrates the use of the IF/THEN/ELSE/IF statement. The books_by_subject() function first uses the provided argument, which should be a book subject, to retrieve the subject ID number of the subject in question. The first IF statement then checks to see if the argument received is the value all.

If the argument variable's value is all, the IF/THEN statement executes extract_all_titles() and assigns the returned list of books and subjects (returned as a text variable) to the found_text variable.

If all was not sent to the function as a parameter, an ELSE IF statement is used to check whether or not the subject ID number that was retrieved is zero or higher. If the value of sub_id is zero or higher, the function executes the statements in the body of the ELSE IF statement, which first use extract_title() to retrieve a list of the titles of all existing books classified under the user's provided subject, and returns the name of the subject with the acquired list of books.

Another ELSE IF statement is then nested within the previous ELSE IF statement, and is executed if the subject ID number has been set to NULL. If sub_id is null, the subject title passed to the function was not found in the booktown database when it was retrieved by the SELECT INTO statement at the function's beginning. In that case, the function returns the string subject not found.

CREATE FUNCTION books_by_subject (text) RETURNS text AS '
  DECLARE
     
     -- Declare an alias for user input, which should be either all
     -- or the name of a subject.
    sub_title ALIAS FOR $1;
     
     -- Declare an integer to store the subject ID in, and a text
     -- variable to store the list of found books.  The text variable
     --  is set to a blank string.
    sub_id INTEGER;
    found_text TEXT :='''';
  
  BEGIN
       
       -- Retrieve the subject ID number for the book matching the
       -- title supplied by the user.
      SELECT INTO sub_id id FROM subjects WHERE subject = sub_title;
       
       -- Check to see if the function was given all as the the subject
       -- name.  If so, execute the SELECT INTO statement and return
       -- the found_text variable.
      IF sub_title = ''all'' THEN
        found_text extract_all_titles();
        RETURN found_text;
         
         -- If the function was NOT sent all as the name of the subject,
         -- check to see the subject ID number turned out to be within
         -- the valid range of subjects. If it did, execute the
         -- extract_title() function with the subject ID number as its
         -- argument, then assign the result to the found_text variable.
        ELSE IF sub_id  >= 0 THEN
          found_text := extract_title(sub_id);
          RETURN  ''\n'' || sub_title || '':\n'' || found_text;
           
           -- If the subject ID number was NULL, return a message telling
           -- the user that the subject specified could not be found.
          ELSE IF sub_id IS NULL THEN
            RETURN ''Subject not found.'';
          END IF;
        END IF;
    END IF;
    RETURN ''An error occurred. .'';
  END;
' LANGUAGE 'plpgsql';

Example 11-41 first shows the result of the books_by_subject function when it is called with all as the argument (an indication that the user wishes to view the books within all defined subjects). The example then shows the results received when Computers is passed as the function's argument (an indication that the user wishes to view only books categorized as computer-related books).

booktown=# SELECT books_by_subject('all');
books_by_subject

Arts:
Dynamic Anatomy

Business:

Children's Books:
The Cat in the Hat
Bartholomew and the Oobleck
Franklin in the Dark
Goodnight Moon

[...]

Science:

Science Fiction:
Dune
2001: A Space Odyssey

(1 row)

booktown=# SELECT books_by_subject('Computers');
                      books_by_subject
--------------------------------------------------------------

Computers:
Learning Python
Perl Cookbook
Practical PostgreSQL
Programming Python

(1 row)

The basic loop

Use the LOOP keyword to begin a basic, unconditional loop within a function. An unconditional loop will execute the statements within its body until an EXIT statement is reached. To form an EXIT statement, the EXIT keyword can be accompanied by WHEN, followed by and an expression to specify when the loop should exit. Th expression should be a Boolean expression, such as one that checks to see whether a variable has reached a specified value. Following is the syntax (without the ELSE keyword) for an unconditional loop:

    LOOP
      statement;
      [...]
    END LOOP;

An unconditional loop statement will continue to loop until it reaches an EXIT statement. EXIT statements explicitly terminate unconditional loops. When terminating a loop with EXIT, you may optionally specify a label and/or a condition on which the loop should exit from.

A label is an arbitrary identifier, prefixed with a pair of less-than symbols (<<) and suffixed with a pair of greater-than symbols (>>). In the case of a loop, it may be placed directly before the loop block begins to identify that loop block with a chosen label. Here is an example of a defined loop with label syntax:

  <<label_name>>
  LOOP
    [ ... ]
  END LOOP;

By providing a label, you can specify which loop to exit when you have several loops nested inside each other (the use of labels in EXIT will only work if you have specified a label for the loop you are attempting to terminate).

By providing a condition in an EXIT statement specifies that the loop should be terminated when the condition is true.

Here is the syntax for an EXIT statement, within a LOOP:

  [ <<label>> ]
  LOOP
    statement;
    [...]
    EXIT [ label ] [ WHEN condition ];
  END LOOP;

Example 11-42 shows a demonstration of an unconditional loop and an EXIT statement that ends it based on a condition. The square_integer_loop() function squares an integer (multiplies the number by itself) until it reaches a value higher than ten thousand. The function then returns the resulting value.

CREATE FUNCTION square_integer_loop (integer) RETURNS integer AS '
  DECLARE
     
     -- Declare aliases for function argument.
    num1 ALIAS FOR $1;
     
     -- Declare an integer to hold the result.
    result integer;
  
  BEGIN
     
     -- Assign the user input number to the result variable.
    result := num1;
    
    LOOP
      result := result * result;
      EXIT WHEN result >= 10000;
    END LOOP;
    
    RETURN result;
  END;
' LANGUAGE 'plpgsql';

Example 11-43 shows the result of invoking square_integer_loop() and passing the value 3 as an argument.

booktown=# SELECT square_integer_loop(3);
 square_integer_loop
---------------------
                6561
(1 row)

The WHILE loop

The WHILE loop is used to loop through a block of statements until a specified condition becomes false. Each time a WHILE loop is entered, its condition will be evaluated before the statement block is executed.

If the condition is evaluated as TRUE, the statements will then be executed. If the condition is never evaluated as false, the statement block will repeatedly executed until the client process that it originated from is terminated. The syntax of the WHILE loop is shown here:

  [ <<label>> ]
  WHILE condition LOOP
    statement;
    [...]
  END LOOP;

In Example 11-44, the add_two_loop( ) function demonstrates the use of a WHILE loop designed to add one to a number until the number reaches a specified value. The starting number and ending number are both supplied by the user as function arguments. The != symbol in Example 11-44 is the inequality operator. That inequality operator indicates that the WHILE loop will run while the result variable is not equal to the high_number variable. In other words, the WHILE loop in Example 11-44 will run until result is equal to high_number.

CREATE FUNCTION add_two_loop (integer, integer) RETURNS integer AS '
  DECLARE
     
     -- Declare aliases for function arguments.
    low_number ALIAS FOR $1;
    high_number ALIAS FOR $2;
     
     -- Declare a variable to hold the result.
    result INTEGER = 0;
  
  BEGIN
     
     -- Add one to the variable result until the value of result is
     -- equal to high_number.
    WHILE result != high_number LOOP
      result := result + 1;
    END LOOP;
    
    RETURN result;
  END;
' LANGUAGE 'plpgsql';

The FOR loop

The FOR loop is arguably the most important loop implemented in PL/pgSQL. Use the FOR loop to iterate a statement block over a range of integers that you specify. The structure of a FOR loop in PL/pgSQL is similar to FOR loops in other procedural languages, such as C.

In a PL/pgSQL FOR loop an integer variable is stated first, to track the iteration of the loop, then the integer range is given, and finally a statement block is provided. The integer variable created to track the loop's iteration is destroyed once the loop exits; it does not have to be declared in the declaration section of the block. The following shows the syntax of the FOR loop:

  [ <<label>> ]
  FOR identifier IN [ REVERSE ] expression1 .. expression2  LOOP
      statement;
      [...]
  END LOOP;

The FOR loop will perform a single iteration for each incremented value of identifier which is in the range of values between, and including, expression1 and expression2. The identifier value will be initialized to the value of expression1, regardless of any prior settings, and incremented by one each iteration. If REVERSE is specified, identifier will be decremented rather than incremented.

The FOR loop can also be used to cycle through the the results of a query. The second FOR loop in Example 11-45 demonstrates using a FOR loop to work with RECORD and %ROWTYPE variables. The syntax of a FOR loop that iterates through RECORD and %ROWTYPE variables is shown in the following syntax:

  [ <<label>> ]
  FOR { record_variable | %rowtype_variable } IN select_statement LOOP
    statement;
    [...]
  END LOOP;

In Example 11-45, the extract_all_titles() function is used to extract a list of all book titles that exist on the database, organized by subject. When a subject has no book titles, a blank line is displayed. The list is returned as a text variable. A FOR loop is utilized within the extract_all_titles() function to cycle through the available subjects by number.

Another FOR loop is nested within the original loop to cycle through the available books and retrieve all books with subject_id values that match the original loop's iteration variable, which represents the current subject ID number the function is scanning for. In Example 11-45, the iteration variable i is initialized to zero because the first subject ID number in our subjects table is 0.

CREATE FUNCTION extract_all_titles2 () RETURNS text AS '
  DECLARE
     
     -- Declare a variable for the subject ID number.
    sub_id INTEGER;
     
     -- Declare a variable to hold the list of titles.
    text_output TEXT = '' '';
     
     -- Declare a variable to hold the subject title.
    sub_title TEXT;
     
     -- Declare a variable to hold records from the  books table.
    row_data books%ROWTYPE;
  
  BEGIN
     
     -- Outer FOR loop: loop through the body of this loop until the
     -- variable i equals 15.  Start the looping at 0.  Essentially,
     --loop the following statements 16 times (once for each subject).
    FOR i IN 0..15 LOOP
       
       -- Retrieve the subject name of the subject with an ID number
       -- that matches the variable i.
      SELECT INTO sub_title subject FROM subjects WHERE id = i;
       
       -- Insert the subject name, a colon, and a new line into the
       -- text_output variable.
      text_output = text_output || ''\n'' || sub_title || '':\n'';
       
       -- Loop through all records in the books table with a subject ID
       -- that matches the variable i.
      FOR row_data IN SELECT * FROM books
        WHERE subject_id = i  LOOP
         
         -- Insert the title of a matching book into the text_output
         -- variable, followed by a newline.
        text_output := text_output || row_data.title || ''\n'';
      
      END LOOP;
    END LOOP;
     
     -- Return the list.
    RETURN text_output;
  END;
' LANGUAGE 'plpgsql';

Example 11-46 shows the code of another function that uses a FOR loop to iterate through the results of a SQL query. With each iteration of the loop the FOR loop in Example 11-46 places the contents of a result row from a query against the books table into the row_data variable, and then inserts the value of the row's title field into the text_output variable.

The loop ends when the last record in books is reached. By the end of the loop, text_output will contain a list of all book titles that match the subject ID number passed to the function. The text_output variable is returned at the end of the function.

CREATE FUNCTION extract_title (integer) RETURNS text AS '
  DECLARE
     
     -- Declare an alias for function argument.
    sub_id ALIAS FOR $1;
     
     -- Declare a variable to hold book titles and set its default
     --  value to a new line.
    text_output TEXT :=''\n'';
     
     -- Declare a variable to hold rows from the
     -- books table.
    row_data books%ROWTYPE;
  
  BEGIN
     
     -- Iterate through the results of a query.
    FOR row_data IN SELECT * FROM books
    WHERE subject_id = sub_id ORDER BY title  LOOP
       
       -- Insert the title of a matching book into the text_output variable.
      text_output := text_output || row_data.title || ''\n'';
    END LOOP;
      
      -- Return the list of books.
    RETURN text_output;
  END;
' LANGUAGE 'plpgsql';

Example 11-47 shows the results of the extract_title() function when 2, which represents "Children's Books" in the subject table, is passed as an argument.

booktown=# SELECT extract_title(2);
                         extract_title
-------------------------------------------------------------------

Bartholomew and the Oobleck
Franklin in the Dark
Goodnight Moon
The Cat in the Hat

(1 row)

The row_data variable is declared as a %ROWTYPE of the books table because it will only be used to hold records from the books table. We could have declared row_data as a RECORD to accomplish the same result, but the RECORD type should be used when you are going to be using the variable for more than just the rows of one specific table:

row_data RECORD;

The extract_title() function will return the same results whether row data is declared as RECORD, or is declared using %ROWTYPE.