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. The 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, the specified action should be taken.
The IF/THEN statement
The IF/THEN 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-36.
Example 11-36. 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-37, 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 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 with the ISBN number the function has already retrieved for the book. The stock amount is then returned and the function ends.
Example 11-37. Using the IF/THEN Statement
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-38 shows the result of the stock_amount function when it is called with the book ID value 7808 and edition number 1.
Example 11-38. Results of the stock_amount() Function
booktown=# SELECT stock_amount(7808,1);
stock_amount
--------------
22
(1 row)Example 11-38 indicates that 22 copies of the title are in stock.
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 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-39.
Example 11-39. Syntax of an IF/THEN/ELSE Statement
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
IF condition THEN
statement;
[...]
ELSE
statement;
[...]
END IF;
END;
' LANGUAGE 'plpgsql';In Example 11-40, essentially the same steps that were taken in Example 11-37 are taken again to retrieve the ISBN number, store it, then use it to retrieve the stock number of the book in question.
Once the stuck 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 stock number 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.
Example 11-40. Using the IF/THEN/ELSE Statement
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-41 shows the result of the check_stock() function when it is called with the book ID value 4513 and edition number 2.
Example 11-41. Results of the in_stock() Function
booktown=# SELECT in_stock(4513,2); in_stock ---------- t (1 row)
Example 11-41 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 allows you to specify a block of statements that should be executed if a condition evaluates to true, as well as a block of statements that should be executed if the condition proves false and another provided condition proves true. The syntax of the IF/THEN/ELSE/IF statement is shown in Example 11-42.
Example 11-42. Syntax of an IF/THEN/ELSE/IF Statement
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
IF condition THEN
statement;
[...]
ELSE IF condition
statement;
[...]
END IF;
END;
' LANGUAGE 'plpgsql';Example 11-43 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 variable to retrieve the subject ID number of the subject in question. The first IF statement then checks to see if the argument variable received was the value all.
If the argument variable's value was 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 a number 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, then return the name of the subject with the recently 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.
 | The two functions used within Example 11-40 are created later in this section as examples of using loops to control program flow. |
Example 11-43. Using the IF/THEN/ELSE/IF Statement
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-44 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).
Example 11-44. Results of the books_by_subject() Function
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)Loops
Loops, like conditional statements, are another method of controlling the flow of functions. Loops use iteration in a number of different ways to accomplish tasks, and through the use of iteration you can greatly expand the functionality of a PL/pgSQL function.
PL/pgSQL implements three iterative loops: the basic loop, the slightly more advanced WHILE loop, and the FOR loop. Of the three, you will most likely be using the FOR loop most often, as it can be applied to a multitude of different programmatic situations, though the other loops are useful to accomplish certain goals.
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 and an expression to specify when the loop should exit, such as a variable reaching a specified value. Example 11-45 shows the syntax for beginning an unconditional loop.
Example 11-45. Unconditional Loop Syntax
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
[ label ]
LOOP
statement;
[...]
END LOOP;
END;
' LANGUAGE 'plpgsql';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 condition on which the loop should exit.
By providing a label, you can specify the level of nesting the EXIT statement should terminate (the use of labels in EXIT will only work if you have specified a label for the loop you are attempting to terminate). Providing a condition in an EXIT statement specifies that the loop should be terminated when the condition is true. The proper syntax of EXIT statements is shown in Example 11-46.
Example 11-46. EXIT Statement Syntax
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
LOOP
statement;
[...]
EXIT [ label ] [ WHEN condition ]
END LOOP;
END;
' LANGUAGE 'plpgsql';Example 11-47 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.
Example 11-47. Using the Basic Loop
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-48 shows the result of the square_integer_loop() when passed 3 as the function's argument.
The While Loop
The WHILE loop is used to loop through a block of statements until a provided 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 in Example 11-49.
Example 11-49. WHILE Loop Syntax
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
WHILE condition LOOP
statement;
[...]
END LOOP;
END;
' LANGUAGE 'plpgsql';In Example 11-50, 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 != symbols in Example 11-50 act as an inequality operator. An 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-50 will run until result is equal to high_number .
Example 11-50. Using the WHILE Loop
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 provided. The structure of a FOR loop in PL/pgSQL is similar to FOR loops in other procedural languages, such as C, but it is not exact. 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 and does not have to be declared in the declaration section of a block. Example 11-51 shows the syntax of the FOR loop.
Example 11-51. Syntax of the FOR Loop
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
FOR identifier IN [ REVERSE ] expression..expression LOOP
statement;
[...]
END LOOP;
END;
' LANGUAGE 'plpgsql';In Example 11-52, the extract_all_titles() function is used to extract a list of all book titles that exist on the database, organized by subject. All subjects for which there are no book titles display a blank line for their associated book titles. 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-52, the iteration variable i is initialized to zero because the first subject ID number in our subjects table is 0.
Example 11-52. Using the FOR Loop
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';The FOR loop can also be used to cycle through the the results of a query. The second FOR loop in Example 11-52 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 Example 11-53.
Example 11-53. Syntax of a FOR Loop Used With RECORD Variables
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
FOR record_variable / %rowtype_variable IN select_statement LOOP
statement;
[...]
END LOOP;
END;
' LANGUAGE 'plpgsql';Example 11-54 shows the code of another function that uses a FOR loop to iterate through the results of a SQL query. The FOR loop in Example 11-54 places a resulting row of the SELECT * FROM books WHERE subject_id = sub_id ORDER BY title query into the row_data variable and inserts the value of the row's title field into the text_output variable with each iteration of the loop.
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.
Example 11-54. Using the FOR Loop with %ROWTYPE
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-55 shows the results of the extract_title() function when it is passed 2 as the argument variable. (The number two represents Children's Books in the subjects table).
Example 11-55. Result of the extract_title() Function
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 within the function's life. We could have declared row_data as a RECORD to accomplish the same result, but as you learned in the Using RECORD and %ROWTYPE section, 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. If we change the row_data variable to a RECORD in the declarations section the result is the same, as shown in Example 11-56.
[...]
-- Declare a variable to hold rows from the
-- books table.
row_data RECORD;
[...]Handling Errors and Exceptions
RAISE statements handle errors and exceptions during a PL/pgSQL function's operation. A RAISE statement sends specified information to the PostgreSQL elog mechanism.
A RAISE statement is also given the level of error it should raise, the string it should send to PostgreSQL (with any substituted variables referenced with the percent sign), and if any substituted variables are listed within the message text, they are listed in a comma delimited list after the closing quotes of the message. RAISE statements are terminated with a semi-colon. The syntax of a RAISE statement is shown in Example 11-57.
Example 11-57. Syntax of the RAISE Statement
CREATE FUNCTION identifier (arguments) RETURNS type AS'
DECLARE
declarations
BEGIN
RAISE level ''message string''[, identifier [...] ];
END;
' LANGUAGE 'plpgsql';Table 11-1 lists the three possible values for the RAISE statement's level and their meanings.
Table 11-1. Possible Level Values
Value | Explanation |
|---|---|
DEBUG | DEBUG level statements send the specified text as a 'DEBUG:' message to the PostgreSQL log and the client program if the client is connected to a database cluster running in debug mode. DEBUG level RAISE statements will be ignored by a database running in production mode. |
NOTICE | NOTICE level statements send the specified text as a 'NOTICE:' message to the PostgreSQL log and the client program in any PostgreSQL operation mode. |
EXCEPTION | EXCEPTION level statements send the specified text as an 'ERROR:' message to the client program and the PostgreSQL database log, then abort the current transaction. |
Example 11-58 shows how to use the three RAISE levels. The first RAISE statement raises a debug level message. The second RAISE statement sends a notice to the user. The third RAISE statement displays an error and throws an exception, causing the function to end and the transaction to be aborted.
Example 11-58. Using the RAISE Statement
CREATE FUNCTION raise_test () RETURNS integer AS '
DECLARE
-- Declare an integer variable for testing.
an_integer INTEGER = 1;
BEGIN
-- Raise a debug level message.
RAISE DEBUG ''The raise_test() function began.'';
an_integer = an_integer + 1;
-- Raise a notice stating that the an_integer variable was changed,
-- then raise another notice stating its new value.
RAISE NOTICE ''Variable an_integer was changed.'';
RAISE NOTICE ''Variable an_integer's value is now %.'',an_integer;
-- Raise an exception.
RAISE EXCEPTION ''Variable % changed. Transaction aborted.'',an_integer;
RETURN 1;
END;
' LANGUAGE 'plpgsql'; Example 11-59 shows the results of the raise_test() function when called from our booktown database.
Calling Functions
The normal syntax to call another PL/pgSQL function is to either reference the function in a SQL SELECT statement or during assignment of a variable, such as the following two statements:
SELECT function_identifier( arguments );
variable_identifier := function_identifier( arguments );
The use of assignments and SELECT statements to execute functions is standard in PL/pgSQL because all functions in a PostgreSQL database must return a variable of some type. Use the PERFORM keyword to call another function and ignore its return data. Example 11-60 shows the syntax of the PERFORM keyword.
Example 11-61 demonstrates the use of PERFORM and how to call another PL/pgSQL function through assignment. The ship_item function is a human-usable wrapper to the add_shipment function that accepts basic information, makes sure the customer and book both exist, then sends the information to add_shipment.
Example 11-61. Using the PERFORM Keyword
CREATE FUNCTION ship_item (text,text,text) RETURNS integer AS '
DECLARE
-- Declare function argument aliases.
l_name ALIAS FOR $1;
f_name ALIAS FOR $2;
book_isbn ALIAS FOR $3;
-- Declare a variable to hold the book ID number. This variable
-- is necessary to check for the existence of the provided ISBN.
book_id INTEGER;
-- Declare a variable to hold the customer ID number. This variable
-- is necessary to check for the existence of the customer.
customer_id INTEGER;
BEGIN
-- Retrieve the customer ID number with a previously created
-- function.
SELECT INTO customer_id get_customer_id(l_name,f_name);
-- If the customer does not exist, return -1 and exit. The
-- get_customer_id function returns a -1 if the customer is not found.
IF customer_id = -1 THEN
RETURN -1;
END IF;
-- Retrieve the ID number of the book with the specified ISBN.
SELECT INTO book_id book_id FROM editions WHERE isbn = book_isbn;
-- If the book does not exist in the system, return a -1.
IF NOT FOUND THEN
RETURN -1;
END IF;
-- If the book and customer both exist, add the shipment.
PERFORM add_shipment(customer_id,book_isbn);
-- Return 1 to indicate the function was successful.
RETURN 1;
END;
' LANGUAGE 'plpgsql';