Function Body

Now that you have an overview of the components of a PL/pgSQL function, let's look at the function body in greater detail. I'll start by showing you how to include documentation (that is, comments) in your PL/pgSQL functions. Next, I'll look at variable declarations. Finally, I'll finish up this section by describing the different kinds of statements that you can use inside of a PL/pgSQL function.

Comments

There are two comment styles in PL/pgSQL. The most frequently seen comment indicator is the double dash: --. A double dash introduces a comment that extends to the end of the current line. For example:

-- This line contains a comment and nothing else
DECLARE
 customer_id INTEGER; -- This is also a comment

-- due_date DATE; -- This entire line is a comment
 -- because it begins with a '--'

PL/pgSQL understands C-style comments as well. A C-style comment begins with the characters /* and ends with the characters */. A C-style comment can span multiple lines:

/*
 NAME: compute_due_date()

 DESCRIPTION: This function will compute the due date for a tape
 rental.

 INPUT: 
 $1 -- Date of original rental

 RETURNS: A date indicating when the rental is due.
*/

CREATE FUNCTION compute_due_date( DATE ) RETURNS DATE 
...

Choosing a comment style is purely a matter of personal preference. Of course, the person choosing the style may not be youyou may have to conform to coding standards imposed by your customer (and/or employer). I tend to use only the double-dash comment style in PL/pgSQL code. If I want to include a multi-line comment, I start each line with a double dash:

----------------------------------------------------------------------
-- NAME: compute_due_date()
--
-- DESCRIPTION: This function will compute the due date for a tape
-- rental.
--
-- INPUT: 
-- $1 -- Date of original rental
--
-- RETURNS: A date indicating when the rental is due.

CREATE FUNCTION compute_due_date( DATE ) RETURNS DATE
...

I find that the double-dash style looks a little cleaner.

Variables

The variable declarations that you've seen up to this point have all been pretty simple. There are actually five ways to introduce a new variable (or at least a new variable name) into a PL/pgSQL function.

• Each parameter defines a new variable.
• You can declare new variables in the DECLARE section of a block.
• You can create an alternate name for a function parameter using the ALIAS statement.
• You can define a new name for a variable (invalidating the old name) using the RENAME statement.
• The iterator variable for an integer-based FOR loop is automatically declared to be an integer.

Let's look at these variables one at a time.

Function Parameters

I mentioned earlier in this chapter that each parameter in a PL/pgSQL function is automatically assigned a name. The first parameter (in left-to-right order) is named $1, the second parameter is named $2, and so on. You define the data type for each parameter in the function definition-for example:

CREATE FUNCTION write_history( DATE, rentals )...

This function expects two parameters. The first parameter is named $1 and is of type DATE. The second parameter is named $2 and is of type rentals. If you're using a newer version of PostgreSQL (8.0 or later), you can also define your own names for function parameters:

CREATE FUNCTION write_history( historyDate DATE, rentalRecord rentals )...

In this case, you've given two names to each parameter. You can refer to the first parameter as historyDate or as $1 and the second parameter as rentalRecord or $2. When you include parameter names in a CREATE FUNCTION command, you're assigning aliases for the parameters without explicitly writing ALIAS commands.

Notice that the write_history() function (in the preceding code line) expects an argument of type rentals. In the sample database, 'rentals' is actually the name of a table. Inside of the write_history() function, you can use the rentalRecord parameter (also known as $2) as if it were a row in the rentals table. That means that you can work with rentalRecord.tape_id, rentalRecord.customer_id, rentalRecord.rental_date, or $2.tape_id, $2.customer_id, and $2.rental_date.

When you call this function, you need to pass a row from the rentals table as the second argument. For example:

SELECT write_history( CURRENT_DATE, rentals ) FROM rentals;

 

DECLARE

The second way to introduce a new variable into a PL/pgSQL function is to list the variable in the DECLARE section of a block. The name of a non-parameter variable can include alphabetic characters (A-Z), underscores, and digits. Variable names must begin with a letter (A-Z or a-z) or an underscore. Names are case-insensitive: my_variable can also be written as My_Variable, and both still refer to the same variable.

The PL/pgSQL documentation mentions that you can force a variable name to be case-sensitive by enclosing it in double quotes. For example, "pi". As of PostgreSQL 7.1.3, this does not seem to work. You can enclose a variable name within double quotes if you need to start the name with a digit.

Oddly enough, you can actually DECLARE a variable whose name starts with a '$', $3, for example, but I wouldn't recommend it; I would expect that this feature (bug?) may be removed (fixed?) at some point in the future.

The complete syntax for a variable declaration is

var-name [CONSTANT] var-type [NOT NULL] [{ DEFAULT | := } expression];

Some of the examples in this chapter have declared variables using the most basic form:

due_date DATE;
rental_period INTERVAL := ''7 days'';

The first line creates a new variable named due_date. The data type of due_date is DATE. Because I haven't explicitly provided an initial value for due_date, it will be initialized to NULL.

The second line defines a new INTERVAL variable named rental_period. In this case, I have provided an initial value, so rental_period will be initialized to the INTERVAL value '7 days'. I could have written this declaration as

rental_period INTERVAL DEFAULT ''7 days'';

In the DECLARE section of a block, DEFAULT is synonymous with ':='.

The initializer expression must evaluate to a value of the correct type. If you are creating an INTEGER variable, the initializer expression must evaluate to an INTEGER value or to a type that can be coerced into an INTEGER value.

In newer versions of PostgreSQL, you can declare array variables by writing a set of square brackets (and an optional element count) following the data type. For example, the declaration

montly_balances NUMERIC(7.2)[12] := '{}';

defines a variable named monthly_balances as an array of 12 numeric values. There is one counter-intuitive quirk that you should know about when you declare an array variable. If you define an array without an initializer, the array is NULLapparently, that's not the same thing as saying that the array is full of NULL values. You can't insert individual values into a NULL array. That means that code such as the following will silently fail:

DECLARE
 monthly_balances NUMERIC(7.2)[12];
BEGIN
 monthly_balances[1] := 10;
 monthly_balances[2] := monthly_balances[1] * 1.10;
 ...

You can't insert a value into a NULL array, but you can copy an entire array over the top of a NULL array:

DECLARE
 new_balances NUMERIC(7.2)[12];
 old_balances NUMERIC(7,2)[12] := '{}';
BEGIN
 new_balances := old_balances;
 ...

So you can only put a value into an array by initializing it or by copying another array over the top of it.

You can define PL/pgSQL functions that take array values as arguments, and you can return array values from PL/pgSQL functions.

Prior to PostgreSQL version 8.0, the DECLARE section had a couple of surprises up its sleeve. First, you could use any of the function parameters in the initializer expression, even if you ALIASed them. The following is illegal:

CREATE FUNCTION compute_due_date(DATE) RETURNS DATE AS '
 DECLARE
 due_date DATE := $1 + ''7 days''::INTERVAL;
 ...

ERROR: Parameter $1 is out of range

The second issue was that once you created a variable in a DECLARE section, you could not use that variable later within the same DECLARE section. That meant that you couldn't do something like

CREATE FUNCTION do_some_geometry(REAL) RETURNS REAL AS '
 DECLARE
 pi CONSTANT REAL := 3.1415926535;
 radius REAL := 3.0;
 diameter REAL := pi * ( radius * radius );
 ...

ERROR: Attribute 'pi' not found

Both of these problems have been fixed in release 8.0.

Notice in the previous example that I declared pi to be a 'CONSTANT REAL'. When you define a variable as CONSTANT, you prevent assignment to that variable. You must provide an initializer for a CONSTANT.

The final modifier for a variable declaration is NOT NULL. Defining a variable to be NOT NULL means that you will receive an error if you try to set that variable to NULL. You must provide an initializer when you create a NOT NULL variable[3].

[3] This makes perfect sense if you think about it. If you don't provide an initializer, PL/pgSQL will initialize each variable to NULLyou can't do that if you have declared the variable to be NOT NULL.

Now you can put all these pieces together. The following declarations are identical in function:

pi CONSTANT REAL NOT NULL DEFAULT 3.1415926535;
pi CONSTANT REAL NOT NULL := 3.1415926535;
pi CONSTANT REAL := 3.1415926535;

Each declares a REAL variable named pi, with an initial value of 3.14159265. The NOT NULL clause is superfluous here because we have declared pi to be a constant and we have given it a non-null initial value; it's not a bad idea to include NOT NULL for documentation purposes.

The default value for a variable is computed each time you enter the block that declares it. If you define a default value in terms of an expression, the variables and functions within that expression can change value from one execution to the next. For example, if an inner block declares a variable whose default value is defined by a variable in an outer block, the default value will vary with the outer variable.

Pseudo Data Types%TYPE and %ROWTYPE

When you create a PL/pgSQL variable, you must declare its data type. Before moving on to the ALIAS command, there are a few pseudo data types that you should know about.

%TYPE lets you define one variable to be of the same type as another. Quite often, you will find that you need to temporarily store a value that you have retrieved from a table, or you might need to make a copy of a function parameter. Let's say that you are writing a function to process a rentals record in some way:

CREATE FUNCTION process_rental( rentals ) RETURNS BOOLEAN AS $$
 DECLARE
 original_tape_id CHAR(8);
 original_customer_id INTEGER;
 original_rental_row ALIAS FOR $1;

 BEGIN

 original_tape_id := original_rental_row.tape_id;
 original_customer_id := original_rental_row.customer_id;
 ...

In this snippet, you are making a local copy of the rentals.tape_id and rentals.customer_id columns. Without %TYPE, you have to ensure that you use the correct data types when you declare the original_tape_id and original_customer_id variables.

That might not sound like such a big deal now, but what about six months later when you decide that eight characters isn't enough to hold a tape ID?

Instead of doing all that maintenance work yourself, you can let PL/pgSQL do the work for you. Here is a much better version of the process_rental() function:

CREATE FUNCTION process_rental( rentals ) RETURNS BOOLEAN AS $$
 DECLARE
 original_tape_id rentals.tape_id%TYPE;
 original_customer_id rentals.customer_id%TYPE;
 original_rental_row ALIAS FOR $1;

 BEGIN

 original_tape_id := original_rental_row.tape_id;
 original_customer_id := original_rental_row.customer_id;
 ...

By using %TYPE, I've told PL/pgSQL to create the original_tape_id variable using whatever type rentals.tape_id is defined to be. I've also created original_customer_id with the same data type as the rentals.customer_id column.

This is an extremely powerful feature. At first blush, it may appear to be just a simple timesaving trick that you can use when you first create a function. The real power behind %TYPE is that your functions become self-maintaining. If you change the data type of the rentals.tape_id column, the process_rentals() function will automatically inherit the change. You won't have to track down all the places where you have made a temporary copy of a tape_id and change the data types.

You can use the %TYPE feature to obtain the type of a column or type of another variable (as shown in the code that follows). You cannot use %TYPE to obtain the type of a parameter. Starting with PostgreSQL version 7.2, you can use %TYPE in the argument list for a function. For example:

CREATE FUNCTION process_rental( rentals, rentals.customer_id%TYPE )
 RETURNS BOOLEAN AS '
 DECLARE
 original_tape_id rentals.tape_id%TYPE;
 original_customer_id rentals.customer_id%TYPE;
 original_rental_row ALIAS FOR $1;
 ...

%TYPE lets you access the data type of a column (or variable). %ROWTYPE provides similar functionality. You can use %ROWTYPE to declare a variable that has the same structure as a row in the given table. For example:

CREATE FUNCTION process_rental( rentals ) RETURNS BOOLEAN AS $$
 DECLARE
 original_tape_id rentals.tape_id%TYPE;
 original_customer_id rentals.customer_id%TYPE;
 original_rental_row rentals%ROWTYPE;
 ...

The original_rental_row variable is defined to have the same structure as a row in the rentals table. You can access columns in original_rental_row using the normal dot syntax: original_rental_row.tape_id, original_rental_row.rental_date, and so on.

Using %ROWTYPE, you can define a variable that has the same structure as a row in a specific table. A bit later in this chapter, I'll show you how to process dynamic queries (see the section "EXECUTE"); that is, a query whose text is not known at the time you are writing your function. When you are processing dynamic queries, you won't know which table to use with %ROWTYPE.

Other Pseudo Types

The RECORD data type is used to declare a composite variable whose structure will be determined at execution time. I'll describe the RECORD type in more detail a bit later (see the section "Loop Constructs").

PostgreSQL version 7.3 introduced a new pseudo type named TRIGGER. A function defined with a return type of trIGGER can only be used as a trigger function. I'll describe trigger functions later in this chapter (see the section titled "Triggers").

The final pseudo data type is OPAQUE. The OPAQUE type can be used only to define the return type of a function[4]. You cannot declare a variable (or parameter) to be of type OPAQUE. In fact, you can use OPAQUE only to define the return type of a trigger function only. OPAQUE is an obsolete name; you should define trigger functions using the TRIGGER type instead.

[4] You can use OPAQUE to define the data type of a function argument, but not when you are creating a PL/pgSQL function. Remember, functions can be defined in a number of different languages.

ALIAS and RENAME

Now, let's move on to the next method that you can use to define a new variable, or a least a new name for an existing variable. You've already seen the ALIAS statement earlier in this chapter. The ALIAS statement creates an alternative name for a function parameter. You cannot ALIAS a variable that is not a function parameter. Using ALIAS, you can define any number of names that equate to a parameter:

CREATE FUNCTION foo( INTEGER ) RETURNS INTEGER AS '
 DECLARE
 param_1 ALIAS FOR $1;
 my_param ALIAS FOR $1;
 arg_1 ALIAS FOR $1;
 BEGIN
 $1 := 42;
 -- At this point, $1, param_1, my_param and arg_1
 -- are all set to 42.
 ...

As we've mentioned already, if you're using PostgreSQL version 8.0 or later, you can skip the ALIAS commands and simply name each parameter in the argument list.

The RENAME statement is similar to ALIAS; it provides a new name for an existing variable. Unlike ALIAS, RENAME invalidates the old variable name. You can RENAME any variable, not just function parameters. The syntax for the RENAME statement is

RENAME old-name TO new-name

Here is an example of the RENAME statement:

CREATE FUNCTION foo( INTEGER ) RETURNS INTEGER AS '
 DECLARE
 RENAME $1 TO param1;
 BEGIN
 ...

Important Note

The RENAME statement does not work in PostgreSQL versions 7.1.2 through at least 7.4, but it appears to function correctly in version 8.0.

RENAME and ALIAS can be used only within the DECLARE section of a block.

FOR Loop Iterator

So far, you have seen four methods for introducing a new variable or a new variable name. In each of the preceding methods, you explicitly declare a new variable (or name) in the DECLARE section of a block and the scope of the variable is the block in which it is defined. The final method is different.

One of the control structures that you will be looking at soon is the FOR loop. The FOR loop comes in two flavorsthe first flavor is used to execute a block of statements some fixed number of times; the second flavor executes a statement block for each row returned by a query. In this section, I will talk only about the first flavor.

Here is an example of a FOR loop:

 FOR i IN 1 .. 12 LOOP
 balance := balance + customers.monthly_balances[i];
 END LOOP;

In this example, you have defined a loop that will execute 12 times. Each statement within the loop (you have only a single statement) will be executed 12 times. The variable i is called the iterator for the loop (you may also see the term loop index to describe the iterator). Each time you go through this loop, the iterator (i) is incremented by 1.

The iterator for an integer FOR loop is automatically declared for you. The type of the iterator is INTEGER. It is important to remember that the iterator for an integer FOR loop is a new variable. If you have already declared a variable with the same name as the iterator, the original variable will be hidden for the remainder of the loop. For example:

...
 DECLARE
 i REAL = 0;
 balance NUMERIC(9,2) = 0;
 BEGIN

 --
 -- At this point, i = 0
 --

 FOR i IN 1 .. 12 LOOP

 --
 -- we now have a new copy of i, it will vary from 1 to 12
 --

 balance := balance + customers.monthly_balances[i]; 
 END LOOP;

 --
 -- Now, if we access i, we will find that it is 
 -- equal to 0 again
 -- 

Notice that while you are inside the loop, there are two variables named ithe inner variable is the loop iterator, and the outer variable was declared inside of this block. If you refer to i inside the loop, you are referring to the inner variable. If you refer to i outside the loop, you are referring to the outer variable. A little later, I'll show you how to access the outer variable from within the loop.

Now that you have seen how to define new variables, it's time to move on. This next section explains each type of statement that you can use in the body of a PL/pgSQL function.

PL/pgSQL Statement Types

At the beginning of this chapter, I said that PL/pgSQL adds a set of procedural constructs to the basic SQL language. In this next section, I'll examine the statement types added by PL/pgSQL. PL/pgSQL includes constructs for looping, exception and error handling, simple assignment, and conditional execution (that is, IF/THEN/ELSE). Although I don't describe them here, it's important to remember that you can also include any SQL command in a PL/pgSQL function.

Assignment

The most commonly seen statement in many programs is the assignment statement. Assignment lets you assign a new value to a variable. The format of an assignment statement should be familiar by now; you've already seen it in most of the examples in this chapter:

target := expression;

target should identify a variable, a function parameter, a column, or in some cases, a row. If target is declared as CONSTANT, you will receive an error. When PL/pgSQL executes an assignment statement, it starts by evaluating the expression. If expression evaluates to a value whose data type is not the same as the data type of target, PL/pgSQL will convert the value to the target type. (In cases where conversion is not possible, PostgreSQL will reward you with an error message.)

The expression is actually evaluated by the PostgreSQL server, not by PL/pgSQL. This means that expression can be any valid PostgreSQL expression. Chapter 2, "Working with Data in PostgreSQL," describes PostgreSQL expressions in more detail.

SELECT INTO

The assignment statement is one way to put data into a variable; SELECT INTO is another. The syntax for a SELECT INTO statement is

SELECT INTO destination [, ...] select-list FROM ...;

A typical SELECT INTO statement might look like this:

...
DECLARE
 customer customers%ROWTYPE;
BEGIN
 SELECT INTO customer * FROM customers WHERE customer_id = 10;
...

When this statement is executed, PL/pgSQL sends the query "SELECT * FROM customers WHERE customer_id = 10" to the server. This query should not return more than one row. The results of the query are placed into the customer variable. Because I specified that customer is of type customers%ROWTYPE, the query must return a row shaped exactly like a customers row; otherwise, PL/pgSQL signals an error.

I could also SELECT INTO a list of variables, rather than into a single composite variable:

DECLARE
 phone customers.phone%TYPE;
 name customers.customer_name%TYPE;
BEGIN
 SELECT INTO name, phone 
 customer_name, customers.phone FROM customers 
 WHERE customer_id = 10;
...

Notice that I had to explicitly request customers.phone in this query. If I had simply requested phone, PL/pgSQL would have assumed that I really wanted to execute the query:

SELECT customer_name, NULL FROM customers where customer_id = 10;

Why? Because I have declared a local variable named phone in this function, and PL/pgSQL would substitute the current value of phone wherever it occurred in the query. Because phone (the local variable) is initialized to NULL, PL/pgSQL would have stuffed NULL into the query. You should choose variable names that don't conflict with column names, or fully qualify column name references.

Of course, you can also SELECT INTO a RECORD variable and the RECORD will adapt its shape to match the results of the query.

I mentioned earlier that the query specified in a SELECT INTO statement must return no more than one row. What happens if the query returns no data? The variables that you are selecting into are set to NULL. You can also check the value of the predefined variable FOUND (described later in this chapter) to determine whether a row was actually retrieved. What happens if the query returns more than one row? If you're using an older version of PostgreSQL, PL/pgSQL will throw an error at you. If you're using PostgreSQL version 8.0 or later, the target variables are filled in with values from the first row returned by the SELECT command.

A bit later in this chapter, you'll see the FOR-IN-SELECT loop that can handle an arbitrary number of rows (see the section "Loop Constructs").

Conditional Execution

Using the IF statement, you can conditionally execute a section of code. The most basic form of the IF statement is

IF expression THEN
 statements
END IF;

The expression must evaluate to a BOOLEAN value or to a value that can be coerced into a BOOLEAN value. If expression evaluates to trUE, the statements between THEN and END IF are executed. If expression evaluates to FALSE or NULL, the statements are not executed.

Here are some sample IF statements:

IF ( now() > rentals.rental_date + rental_period ) THEN
 late_fee := handle_rental_overdue();
END IF;

IF ( customers.balance > maximum_balance ) THEN
 PERFORM customer_over_balance( customers );
 RETURN( FALSE );
END IF;

In each of these statements, the condition expression is evaluated by the PostgreSQL server. If the condition evaluates to trUE, the statements between THEN and END IF are executed; otherwise, they are skipped and execution continues with the statement following the END IF.

You can also define a new block within the IF statement:

IF ( tapes.dist_id IS NULL ) THEN
 DECLARE 
 default_dist_id CONSTANT integer := 0;
 BEGIN
 ...
 END;
END IF;

The obvious advantage to defining a new block within an IF statement is that you can declare new variables. It's usually a good idea to declare variables with the shortest possible scope; you won't pollute the function's namespace with variables that you need in only a few places, and you can assign initial values that may rely on earlier computations.

The next form of the IF statement provides a way to execute one section of code if a condition is trUE and a different set of code if the condition is not trUE. The syntax for an IF-THEN-ELSE statement is

IF expression THEN
 statements_1
ELSE
 statements_2
END IF;

In this form, statements_1 will execute if expression evaluates to trUE; otherwise, statements_2 will execute. Note that statements_2 will not execute if the expression is trUE. Here are some sample IF-THEN-ELSE statements:

IF ( now() > rentals.rental_date + rental_period ) THEN
 late_fee := handle_rental_overdue();
ELSE
 late_fee := 0;
END IF;

IF ( customers.balance > maximum_balance ) THEN
 PERFORM customer_over_balance( customers );
 RETURN( FALSE );
ELSE
 rental_ok = TRUE; 
END IF;

An IF-THEN-ELSE is almost equivalent to two IF statements. For example, the following

IF ( now() > rentals.rental_date + rental_period ) THEN
 statements_1
ELSE 
 statements_2
END IF;

is nearly identical to

IF ( now() > rentals.rental_date + rental_period ) THEN
 statements_1
END IF;

IF ( now() <= rentals.rental_date + rental_period ) THEN
 statements_2
END IF;

The difference between these two scenarios is that using IF-THEN-ELSE, the condition expression is evaluated once; but using two IF statements, the condition expression is evaluated twice. In many cases, this distinction won't be important; but in some circumstances, the condition expression may have side effects (such as causing a trigger to execute), and evaluating the expression twice will double the side effects.

You can nest IF-THEN-ELSE statements:

IF ( today > compute_due_date( rentals )) THEN
 --
 -- This rental is past due
 --
 ...
ELSE
 IF ( today = compute_due_date( rentals )) THEN
 --
 -- This rental is due today
 --
 ...
 ELSE
 --
 -- This rental is not late and it's not due today
 --
 ...
 END IF;
END IF;

PostgreSQL versions 7.2 and later support a more convenient way to nest IF-THEN-ELSE-IF statements:

IF ( today > compute_due_date( rentals )) THEN
 --
 -- This rental is past due
 --
 ...
ELSIF ( today = compute_due_date( rentals )) THEN
 --
 -- This rental is due today
 --
 ...
ELSE
 --
 -- This rental is not late and it's not due today
 --
 ...
END IF;

The ELSIF form is functionally equivalent to a nested IF-THEN-ELSE-IF but you need only a single END IF statement. Notice that the spelling is ELSIF, not ELSE IF. You can include as many ELSIF sections as you like.

Loop Constructs

Next, let's look at the loop constructs offered by PL/pgSQL. Using a loop, you can repeat a sequence of statements until a condition occurs. The most basic loop construct is the LOOP statement:

[<>]
LOOP
 statements
END LOOP;

In this form, the statements between LOOP and END LOOP are repeated until an EXIT or RETURN statement exits the loop. If you don't include an EXIT or RETURN statement, your function will loop forever. I'll explain the optional <> in the section that covers the EXIT statement.

You can nest loops as deeply as you need:

 1 row := 0;
 2
 3 LOOP
 4 IF( row = 100 ) THEN
 5 EXIT;
 6 END IF;
 7
 8 col := 0;
 9
10 LOOP
11 IF( col = 100 ) THEN
12 EXIT;
13 END IF;
14 
15 PERFORM process( row, col );
16
17 col := col + 1;
18
19 END LOOP;
20
21 row := row + 1;
22 END LOOP;
23
24 RETURN( 0 );

In the preceding code snippet, there are two loops. Because the inner loop is completely enclosed within the outer loop, the inner loop executes each time the outer loop repeats. The statements in the outer loop execute 100 times. The statements in the inner loop (lines 10 through 19) execute 100 x 100 times.

The EXIT statement at line 5 causes the outer LOOP to terminate; when you execute that statement, execution continues at the statement following the END LOOP for the enclosing loop (at line 24). The EXIT statement at line 12 will change the point of execution to the statement following the END LOOP for the enclosing loop (at line 21).

I'll cover the EXIT statement in more detail in the next section.

The next loop construct is the WHILE loop. The syntax for a WHILE loop is

[<>]
WHILE expression LOOP
 statements
END LOOP;

The WHILE loop is used more frequently than a plain LOOP. A WHILE loop is equivalent to

[<>]
LOOP

 IF( NOT ( expression )) THEN
 EXIT;
 END IF;

 statements

END LOOP;

The condition expression must evaluate to a BOOLEAN value or to a value that can be coerced to a BOOLEAN. The expression is evaluated each time execution reaches the top of the loop. If expression evaluates to trUE, the statements within the loop are executed. If expression evaluates to FALSE or NULL, execution continues with the statement following the END LOOP.

Here is the nested loop example again, but this time, I have replaced the IF tests with a WHILE loop:

 1 row := 0;
 2
 3 WHILE ( row < 100 ) LOOP
 4
 5 col := 0;
 6
 7 WHILE ( col < 100 ) LOOP
 8
 9 PERFORM process( row, col );
10
11 col := col + 1;
12
13 END LOOP;
14
15 row := row + 1;
16 END LOOP;
17
18 RETURN( 0 );

You can see that the WHILE loop is much neater and easier to understand than the previous form. It's also a lot easier to introduce a bug if you use a plain LOOP and have to write the IF tests yourself.

The third loop construct is the FOR loop. There are two forms of the FOR loop. In the first form, called the integer-FOR loop, the loop is controlled by an integer variable:

[<>]
FOR iterator IN [ REVERSE ] start-expression .. end-expression LOOP
 statements
END LOOP;

In this form, the statements inside the loop are repeated while the iterator is less than or equal to end-expression (or greater than or equal to if the loop direction is REVERSE). Just before the first iteration of the loop, iterator is initialized to start-expression. At the bottom of the loop, iterator is incremented by 1 (or -1 if the loop direction is REVERSE); and if within the end-expression, execution jumps back to the first statement in the loop.

An integer-FOR loop is equivalent to:

[<>]
DECLARE
 Iterator INTEGER;
 increment INTEGER;
 end_value INTEGER;
BEGIN
 IF( loop-direction = REVERSE ) THEN
 increment := -1;
 ELSE
 increment := 1;
 END IF;

 iterator := start-expression;
 end_value := end-expression;

 LOOP
 IF( iterator >= end_value ) THEN
 EXIT;
 END IF;

 statements

 iterator := iterator + increment;

 END LOOP;
END;

The start-expression and end-expression are evaluated once, just before the loop begins. Both expressions must evaluate to an INTEGER value or to a value that can be coerced to an INTEGER.

Here is the example code snippet again, this time written in the form of an integer-FOR loop:

 1 FOR row IN 0 .. 99 LOOP
 2
 3 FOR col in 0 .. 99 LOOP
 4
 5 PERFORM process( row, col );
 6
 8 END LOOP;
 9
10 END LOOP;
11
12 RETURN( 0 );

This version is more readable than the version that used a WHILE loop. All the information that you need in order to understand the loop construct is in the first line of the loop. Looking at line 1, you can see that this loop uses a variable named row as the iterator; and unless something unusual happens inside the loop, row starts at 0 and increments to 99.

There are a few points to remember about the integer-FOR loop. First, the iterator variable is automatically declaredit is defined to be an INTEGER and is local to the loop. Second, you can terminate the loop early using the EXIT (or RETURN) statement. Third, you can change the value of the iterator variable inside the loop: Doing so can affect the number of iterations through the loop.

You can use this last point to your advantage. In PL/pgSQL, there is no way to explicitly specify a loop increment other than 1 (or -1 if the loop is REVERSEd). But you can change the effective increment by modifying the iterator within the loop. For example, let's say that you want to process only odd numbers inside a loop:

 1 ...
 2 FOR i IN 1 .. 100 LOOP
 3 ...
 4 i := i + 1;
 5 ...
 6 END LOOP;
 7 ...

The first time you go through this loop, i will be initialized to 1. At line 4, you increment i to 2. When you reach line 6, the FOR loop will increment i to 3 and then jump back to line 3 (the first line in the loop). You can, of course, increment the loop iterator in whatever form you need. If you fiddle with the loop iterator, be sure to write yourself a comment that explains what you're doing.

The second form of the FOR loop is used to process the results of a query. The syntax for this form is

[<>]
FOR iterator IN query LOOP
 statements
END LOOP;

In this form, which I'll call the FOR-IN-SELECT form, the statements within the loop are executed once for each row returned by the query. query must be a SQL SELECT command. Each time through the loop, iterator will contain the next row returned by the query. If the query does not return any rows, the statements within the loop will not execute.

The iterator variable must either be of type RECORD or of a %ROWTYPE that matches the structure of a row returned by the query. Even if the query returns a single column, the iterator must be a RECORD or a %ROWTYPE.

Here is a code snippet that shows the FOR statement:

 1 DECLARE
 2 rental rentals%ROWTYPE;
 3 BEGIN
 4
 5 FOR rental IN SELECT * FROM rentals ORDER BY rental_date LOOP
 6 IF( rental_is_overdue( rental )) THEN
 7 PERFORM process_late_rental( rental );
 8 END IF;
 9 END LOOP;
10
11 END;

A %ROWTYPE iterator is fine if the query returns an entire row. If you need to retrieve a partial row, or you want to retrieve the result of a computation, declare the iterator variable as a RECORD. Here is an example:

 1 DECLARE
 2 my_record RECORD;
 3 BEGIN
 4
 5 FOR my_record IN 
 6 SELECT tape_id, compute_due_date(rentals) AS due_date FROM rentals
 7 LOOP
 8 PERFORM 
 9 check_for_late_rental( my_record.tape_id, my_record.due_date );
10 END LOOP;
11
12 END;

A RECORD variable does not have a fixed structure. The fields in a RECORD variable are determined at the time that a row is assigned. In the previous example, you assign a row returned by the SELECT to the my_record RECORD. Because the query returns two columns, my_record will contain two fields: tape_id and due_date. A RECORD variable can change its shape. If you used the my_record variable as the iterator in a second FOR-IN-SELECT loop in this function, the field names within the RECORD would change. For example:

 1 DECLARE
 2 my_record RECORD;
 3 BEGIN
 4
 5 FOR my_record IN SELECT * FROM rentals LOOP
 6 -- my_record now holds a row from the rentals table
 7 -- I can access my_record.tape_id, my_record.rental_date, etc.
 8 END LOOP;
 9
10 FOR my_record IN SELECT * FROM tapes LOOP
11 -- my_record now holds a row from the tapes table
12 -- I can now access my_record.tape_id, my_record.title, etc.
13 END LOOP;
12 END;

You also can process the results of a dynamic query (that is, a query not known at the time you write the function) in a FOR loop. To execute a dynamic query in a FOR loop, the syntax is a bit different:

[<>]
FOR iterator IN EXECUTE query-string LOOP
 statements
END LOOP;

Notice that this is nearly identical to a FOR-IN loop. The EXECUTE keyword tells PL/pgSQL that the following string may change each time the statement is executed. The query-string can be an arbitrarily complex expression that evaluates to a string value; of course, it must evaluate to a valid SELECT statement. The following function shows the FOR-IN-EXECUTE loop:

 1 CREATE OR REPLACE FUNCTION my_count( VARCHAR ) RETURNS INTEGER AS '
 2 DECLARE
 3 query ALIAS FOR $1;
 4 count INTEGER := 0;
 5 my_record RECORD;
 6 BEGIN
 7 FOR my_record IN EXECUTE query LOOP
 8 count := count + 1;
 9 END LOOP;
10 RETURN count;
11 END;
12 ' LANGUAGE 'plpgsql'; 

 

EXIT

An EXIT statement (without any operands) terminates the enclosing block, and execution continues at the statement following the end of the block.

The full syntax for the EXIT statement is

EXIT [label] [WHEN boolean-expression];

All the EXIT statements that you have seen in this chapter have been simple EXIT statements. A simple EXIT statement unconditionally terminates the most closely nested block.

If you include WHEN boolean-expression in an EXIT statement, the EXIT becomes conditionalthe EXIT occurs only if boolean-expression evaluates to TRUE. For example:

1 FOR i IN 1 .. 12 LOOP
2 balance := customer.customer_balances[i];
3 EXIT WHEN ( balance = 0 );
4 PERFORM check_balance( customer, balance );
5 END LOOP;
6
7 RETURN( 0 );

When execution reaches line 3, the WHEN expression is evaluated. If the expression evaluates to TRUE, the loop will be terminated and execution will continue at line 7.

This statement should really be named EXIT...IF. The EXIT...WHEN expression is not evaluated after each statement, as the name might imply.

LabelsEXIT Targets and Name Qualifiers

Now let's turn our attention to the subject of labels. A label is simply a string of the form

<>

You can include a label prior to any of the following:

• A DECLARE section
• A LOOP
• A WHILE loop
• An integer FOR loop
• A FOR...SELECT loop

A label can perform two distinct functions. First, a label can be referenced in an EXIT statement. For example:

 1 <> 
 2 FOR row IN 0 .. 99 LOOP
 3
 4 <>
 5 FOR col in 0 .. 99 LOOP
 6
 7 IF( process( row, col ) = FALSE ) THEN
 8 EXIT row_loop;
 9 END IF;
10
11 END LOOP;
12
13 END LOOP;
15
15 RETURN( 0 );

Normally, an EXIT statement terminates the most closely nested block (or loop). When you refer to a label in an EXIT statement, you can terminate more than one nested block. When PL/pgSQL executes the EXIT statement at line 8, it will terminate the <> block and the <> block. You can't EXIT a block unless it is active: In other words, you can't EXIT a block that has already ended or that has not yet begun.

The second use for a label has to do with variable scoping. Remember that an integer-FOR loop creates a new copy of the iterator variable. If you have already declared the iterator variable outside of the loop, you can't directly access it within the loop. Consider the following example:

 1 <>
 2 DECLARE
 3 month_num INTEGER := 6;
 4 BEGIN
 5 FOR month_num IN 1 .. 12 LOOP
 6 PERFORM compute_monthly_info( month_num );
 7 END LOOP;
 8 END;

Line 2 declares a variable named month_num. When execution reaches line 4, PL/pgSQL will create a second variable named month_num (and this variable will vary between 1 and 12). Within the scope of the new variable (between lines 4 and 6), any reference to month_num will refer to the new variable created at line 4. If you want to refer to the outer variable, you can qualify the name as func.month_num. In general terms, you can refer to any variable in a fully qualified form. If you omit the label qualifier, a variable reference refers to the variable with the shortest lifetime (that is, the most recently created variable).

RETURN

Every PL/pgSQL function must terminate with a RETURN statement. There are two forms for the RETURN statement:

RETURN expression;
RETURN;

Use the first form when you're writing a PL/pgSQL function that returns a simple value and the second form when you're writing a function returns a SETOF values. If your function returns a SETOF values, you'll use the RETURN NEXT statement (described in the next section) to build up a result set as you go.

When a RETURN statement executes, four things happen:

1. The expression (if any) is evaluated and, if necessary, coerced into the appropriate data type. The RETURN type of a function is declared when you create the function. In the example "CREATE FUNCTION func() RETURNS INTEGER ...", the RETURN type is declared to be an INTEGER. If the RETURN expression does not evaluate to the declared RETURN type, PL/pgSQL will try to convert it to the required type. If you are writing a function that returns a SETOF values, you should omit the expression.
2. The current function terminates. When a function terminates, all code blocks within that function terminate, and all variables declared within that function are destroyed.
3. The return value (obtained by evaluating expression or executing some number of RETURN NEXT statements) is returned to the caller. If the caller assigns the return value to a variable, the assignment completes. If the caller uses the return value in an expression, the caller uses the return value to evaluate the expression. If the function was called by a PERFORM statement, the return value is discarded.
4. The point of execution returns to the caller.

If you fail to execute a RETURN statement, you will receive an error (control reaches end of function without RETURN). You can include many RETURN statements in a function, but only one will execute: whichever RETURN statement is reached first.

RETURN NEXT

If you've defined a function that returns a SETOF values, you don't use the RETURN statement to give a value to the caller. Instead, you execute a series of zero or more RETURN NEXT statements. The syntax for a RETURN NEXT statement is

RETURN NEXT expression;

Each time you execute a RETURN NEXT statement, PL/pgSQL evaluates the expression and adds the result to the function's result set. If you are returning a SETOF rows, expression must evaluate to a row value. If you are returning a SETOF arrays, each expression must evaluate to an array (of the proper type). If you are returning a SETOF simple values, each expression must evaluate to a simple value of the appropriate type. If you are returning a SETOF anyarray or anyelement, see the discussion of polymorphic functions later in this chapter.

When you have finished building the result set, simply RETURN from the function.The following example defines a function that returns the monthly balances for a given customer in the form of a SETOF NUMERIC values:

CREATE OR REPLACE FUNCTION getBalances( id INTEGER ) RETURNS SETOF NUMERIC AS $$
 DECLARE
 customer customers%ROWTYPE;
 BEGIN

 SELECT * FROM customers INTO customer WHERE customer_id = id;

 FOR month IN 1..12 LOOP

 IF customer.monthly_balances[month] IS NOT NULL THEN
 RETURN NEXT customer.monthly_balances[month];
 END IF;

 END LOOP;

 RETURN;

 END;
$$ LANGUAGE 'plpgsql';

Notice that this function will execute the RETURN NEXT statement anywhere from 0 to 12 timesthat means that the result set built by this function may contain anywhere from 0 to 12 rows. If you don't execute a RETURN NEXT statement, the result set built by the function will be empty.

A function that returns a SETOF values acts like a table. That means that a SETOF function is typically written to the right of the FROM in a SELECT command. For example, to call the getBalances() function you just saw, you would write a query such as the following. (Note: These queries won't work for you unless you've added a monthly_balances array to the customers table):

movies=# SELECT customer_id, customer_name, balance, monthly_balances 
movies-# FROM customers;
customer_id | customer_name | balance | monthly_balances
------------+---------------------+---------+------------------
 1 | Jones, Henry | 0.00 |
 4 | Wonderland, Alice N.| 3.00 |
 2 | Rubin, William | 15.00 |
 3 | Panky, Henry | 0.00 | {5.00,52.20}
(4 rows)

movies=# SELECT * FROM getBalances( 3 );
getbalances
-------------
 5.00
 52.20
(2 rows)

movies=# SELECT * FROM getBalances( 2 );
getbalances
-------------
(0 rows)

Notice that the first call to getBalances( 3 ) returned two rows because there are two entries in the monthly_balances column for customer number 3. The second call returned zero rows.

PERFORM

A function written in PL/pgSQL can contain SQL commands intermingled with PL/pgSQL-specific statements. Remember, a SQL command is something like CREATE TABLE, INSERT, UPDATE, and so on; whereas PL/pgSQL adds procedural statements such as IF, RETURN, or WHILE. If you want to create a new table within a PL/pgSQL function, you can just include a CREATE TABLE command in the code:

CREATE FUNCTION process_month_end( ) RETURNS BOOLEAN AS '
 BEGIN
 ...
 CREATE TABLE temp_data ( ... );
 ...
 DROP TABLE temp_data;
 ...
 END;
' LANGUAGE 'plpgsql';

You can include almost any SQL command just by writing the command inline. The exception is the SELECT command. A SELECT command retrieves data from the server. If you want to execute a SELECT command in a PL/pgSQL function, you normally provide variables to hold the results:

DECLARE
 Customer customers%ROWTYPE;
BEGIN
 ...
 SELECT INTO customer * FROM customers WHERE( customer_id = 1 );
 --
 -- The customer variable will now hold the results of the query
 --
 ...
END;

On rare occasions, you may need to execute a SELECT statement, but you want to ignore the data returned by the query. Most likely, the SELECT statement that you want to execute will have some side effect, such as executing a function. You can use the PERFORM statement to execute an arbitrary SELECT command without using the results. For example:

...
 PERFORM SELECT my_function( rentals ) FROM rentals;
...

You can also use PERFORM to evaluate an arbitrary expression, again discarding the results:

...
 PERFORM record_timestamp( timeofday() );
...

 

EXECUTE

The EXECUTE statement is similar to the PERFORM statement. Although the PERFORM statement evaluates a SQL expression and discards the results, the EXECUTE statement executes a dynamic SQL command, and then discards the results. The difference is subtle but important. When the PL/pgSQL processor compiles a PERFORM expression statement, the query plan required to evaluate the expression is generated and stored along with the function. This means that expression must be known at the time you write your function. The EXECUTE statement, on the other hand, executes a SQL statement that is not known at the time you write your function. You may, for example, construct the text of a SQL statement within your function, or you might accept a string value from the caller and then execute that string.

Here is a function that uses the EXECUTE command to time the execution of a SQL command:

 1 CREATE FUNCTION time_command( VARCHAR ) RETURNS INTERVAL AS '
 2 DECLARE
 3 beg_time TIMESTAMP;
 4 end_time TIMESTAMP;
 5 BEGIN
 6
 7 beg_time := timeofday( );
 8 EXECUTE $1;
 9 end_time := timeofday( );
10
11 RETURN( end_time - beg_time );
12 END;
13 ' LANGUAGE 'plpgsql';

You would call the time_command() function like this:

movies=# SELECT time_command( 'SELECT * FROM rentals' );
time_command
--------------
 00:00:00.82
(1 row)

With the EXECUTE statement, you can execute any SQL command (including calls to PL/pgSQL functions) and the results will be discarded, except for the side effects.

GET DIAGNOSTICS

PL/pgSQL provides a catch-all statement that gives you access to various pieces of result information: GET DIAGNOSTICS. Using GET DIAGNOSTICS, you can retrieve a count of the rows affected by the most recent UPDATE or DELETE command and the object-ID of the most recently inserted row. The syntax for the GET DIAGNOSTICS statement is

GET DIAGNOSTICS variable = [ROW_COUNT|RESULT_OID], ...;

ROW_COUNT is meaningless until you have executed an UPDATE or DELETE command. Likewise, RESULT_OID is meaningless until you execute an INSERT command.

Error Handling

PostgreSQL version 8.0 introduced a new error-handling scheme to PL/pgSQL. Prior to version 8.0, any error that occurred during a PL/pgSQL function would abort the function and the transaction that called the function. Beginning with version 8.0, you can intercept error conditions (PL/pgSQL calls them exceptions) and handle them gracefully.

To trap an exception, include an EXCEPTION section just before the END of a block. The syntax for an EXCEPTION section is

EXCEPTION
 WHEN condition [OR condition...] THEN
 statements
 [ WHEN condition [OR condition...] THEN
 statements
 ...
 ]

The condition is derived from the error descriptions listed in Appendix A of the PostgreSQL reference documentation. Table 7.1 shows an excerpt from Appendix A. To convert one of these errors into a condition, just find the error code that you want to trap and write the error description, replacing each space with an underscore.

Table 7.1. Sample PostgreSQL Error Codes

Error Code	Description
Class 08	Connection Exception
08000	CONNECTION EXCEPTION
08003	CONNECTION DOES NOT EXIST
08006	CONNECTION FAILURE
08001	SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION
08004	SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION
08007	TRANSACTION RESOLUTION UNKNOWN
08P01	PROTOCOL VIOLATION

For example, to trap error 08006, you would write an EXCEPTION section like this:

BEGIN
 ...
EXCEPTION
 WHEN connection_failure THEN
 RAISE ERROR 'Connection To Server Lost';
 END;

If any of the statements between BEGIN and EXCEPTION throws a connection_failure error, PL/pgSQL immediately jumps to the first statement in the exception handler (in this case, the RAISE ERROR statement), bypassing the rest of the statements in the block.

You can't trap every condition listed in Appendix A; in particular, you can't trap successful_completion, any of the conditions listed in the WARNING category, or any of the conditions listed in the NO DATA category.

You can trap a whole category of error conditions by writing an EXCEPTION handler for that category. You can distinguish between errors and categories by looking at the last digit of the error code. If the last digit is a 0, you're looking at a category. To trap any of the errors in the connection_exception class, just write an EXCEPTION section like this:

BEGIN
 ...
EXCEPTION
 WHEN connection_exception THEN
 RAISE ERROR 'Something went wrong with the server connection';
 END;

That sequence is equivalent to:

BEGIN
 ...
EXCEPTION
 WHEN
 connection_does_not_exist OR
 connection_failure OR
 sql_client_unable_to_establish_sql_connection OR
 sql_server_rejected_establishment_of_sql_connection OR
 transaction_resolution_unkown OR
 protocol_violation
 RAISE ERROR 'Something went wrong with the server connection';
 END;

PL/pgSQL defines a catch-all condition, named others, that you can use to trap any exceptions not trapped by another handler.

A single exception may match multiple exception handlers. For example, consider the following EXCEPTION section:

BEGIN
 ...
EXCEPTION
 WHEN connection_failure THEN
 RAISE ERROR 'Connection Lost;
 WHEN connection_exception THEN
 RAISE ERROR 'Something went wrong with the server connection';
 WHEN others THEN
 RAISE ERROR 'Something broke';
 END;

If a connection_failure occurs, all three handlers match the exception: The connection_failure handler matches exactly; the connection_exception handler matches because a connection_failure is a member of the connection_exception category; and the others handler matches because others will match any exception. Which handler executes? The first one that matches. That means that you should always list the handlers from most-specific to most-general. If you were to write the others handler first, the connection_failure and connection_exception handlers could never execute.

Remember that you can nest blocks within a single PL/pgSQL function. Each block can have its own EXCEPTION section. When an exception occurs, PL/pgSQL searches through the currently active blocks to find a handler for that exception. If the first (most deeply nested) block hasn't defined a handler for the exception, PL/pgSQL aborts the first block and looks at the surrounding block. If that block hasn't defined a handler for the exception, PL/pgSQL aborts the second block as well and continues to the next block. If PL/pgSQL can't find a handler, it aborts the entire function and reports the exception to the caller of the function.

When a PL/pgSQL function enters a block that includes an EXCEPTION section, it creates a "subtransaction" by executing the internal equivalent of a SAVEPOINT command. If you have one block nested within another (and each block defines exception handlers), you have two subtransactions, one nested within the other. If an exception occurs, PL/pgSQL rolls back nested subtransactions as it searches for an exception handler. When PL/pgSQL finds an exception handler, it executes the handler and rolls back that subtransaction as well. Consider the following code snippet:

...
FOR tape IN SELECT * FROM tapes LOOP
 BEGIN

 update_tape( tape );

 FOR rental IN SELECT * FROM rentals WHERE rentals.tape_id = tape.tape_id LOOP

 BEGIN

 update_rental_1( rental );
 update_rental_2( rental );

 EXCEPTION
 WHEN insufficient_privilege THEN
 RAISE NOTICE 'Privilege denied';
 END;

 END LOOP;
 EXCEPTION
 WHEN others THEN
 RAISE NOTICE 'Unable to process all tapes';
 END;
END LOOP;
...

This snippet contains two loops, one nested within the other. The outer loop reads through the tapes table and, for each tape, calls a function named update_tape() (presumably another PL/pgSQL function). The inner loop reads each rentals record for the current tape and calls two functions with each rental.

Every time the PL/pgSQL interpreter executes the first BEGIN statement, it creates a new subtransaction which we'll call Touter. Likewise, every time PL/pgSQL executes the second BEGIN statement, it creates a new subtransaction, Tinner, nested within Touter. Now consider what happens when an exception occurs.

If the update_rental_2() function throws an exception, PL/pgSQL aborts Tinner (rolling back any changes made by update_rental_1() and update_rental_2()) and then searches for a handler that matches the exception. If update_rental_2() throws an insufficient_privilege exception, PL/pgSQL finds the inner-most exception handler, jumps to the first RAISE NOTICE statement, and then moves on to the statement following the inner-most block. If update_rental_2() throws any other exception, PL/pgSQL ignores the inner-most exception handler (because it doesn't match the exception), aborts Touter (rolling back any changes made by update_tape(), update_rental_1(), and update_rental_2()), jumps to the second RAISE NOTICE statement, and moves on to the statement following the outer-most block.

If the update_tape() function throws an exception, PL/pgSQL aborts Touter (rolling back any changes made by update_tape()), jumps to the second RAISE NOTICE statement, and then moves on to the statement following the outer-most block.

Notice that an exception always aborts the inner-most subtransaction. PL/pgSQL will continue aborting nested subtransactions until it finds a handler for the exception. If no handler is found, the entire transaction is aborted. By using nested subtransactions (and nested exception handlers) in this way, the inner subtransaction contains all of the updates for a single rental. If the inner subtransaction aborts, only those changes made to the current rental are rolled back. The outer subtransaction contains all of the updates for a single tape (including all of the updates for all rentals of that tape). If you abort the outer subtransaction all changes made to the tape are rolled back and all changes made to the rentals of that tape are rolled back as well.

RAISE

Even though PL/pgSQL doesn't offer a way to intercept errors, it does provide a way to generate an error: the RAISE statement. Exceptions are usually generated when an error occurs while executing an SQL (or PL/pgSQL) statement, but you can explicitly raise an exception using the RAISE statement. The syntax for a RAISE statement is

RAISE severity 'message' [, variable [...]];

The severity determines how far the error message will go and whether the error should abort the current transaction.

Valid values for severity are

• DEBUG The message is written to the server's log file and otherwise ignored. The function runs to completion, and the current transaction is not affected.
• NOTICE The message is written to the server's log file and sent to the client application. The function runs to completion, and the current transaction is not affected.
• EXCEPTION The message is written to the server's log file and PL/pgSQL throws a raise_exception exception that you can trap with an EXCEPTION handler as described in the previous section.

The message string must be a literal valueyou can't use a PL/pgSQL variable in this slot, and you cannot include a more complex expression. If you need to include variable information in the error message, you can sneak it into the message by including a % character wherever you want the variable value to appear. For example:

rentals.tape_id := ''AH-54706'';
RAISE DEBUG ''tape_id = %'', rentals.tape_id;

When these statements are executed, the message tape_id = AH-54706 will be written to the server's log file. For each (single) % character in the message string, you must include a variable. If you want to include a literal percent character in the message, write it as %%. For example:

percentage := 20;
RAISE NOTICE ''Top (%)%%'', percentage;

translates to Top (20)%.

The RAISE statement is useful for debugging your PL/pgSQL code; it's even better for debugging someone else's code. I find that the DEBUG severity is perfect for leaving evidence in the server log. When you ship a PL/pgSQL function to your users, you might want to leave a few RAISE DEBUG statements in your code. This can certainly make it easier to track down an elusive bug (remember, users never write down error messages, so you might as well arrange for the messages to appear in a log file). I use the RAISE NOTICE statement for interactive debugging. When I am first building a new PL/pgSQL function, the chances are very slim that I'll get it right the first time. (Funny, it doesn't seem to matter how trivial or complex the function is.) I start out by littering my code with RAISE NOTICE statements; I'll usually print the value of each function parameter as well as key information from each record that I SELECT. As it becomes clearer that my code is working, I'll either remove or comment out (using "--") the RAISE NOTICE statements. Before I send out my code to a victim, er, user, I'll find strategic places where I can leave RAISE DEBUG statements. The RAISE DEBUG statement is perfect for reporting things that should never happen. For example, because of the referential integrity that I built into the tapes, customers, and rentals tables, I should never find a rentals record that refers to a nonexistent customer. I'll check for that condition (a missing customer) and report the error with a RAISE DEBUG statement. Of course, in some circumstances, a missing customer should really trigger a RAISE EXCEPTIONif I just happen to notice the problem in passing and it really doesn't affect the current function, I'll just note it with a RAISE DEBUG. So, the rule I follow is: if the condition prevents further processing, I RAISE an EXCEPTION; if the condition should never happen, I RAISE a DEBUG message; if I am still developing my code, I RAISE a NOTICE.