Declaration

For variables to be available to the code within a PL/pgSQL code block, they must be declared in the declarations section of the block, which is denoted by the DECLARE keyword at the beginning of the block. Variables declared in a block will be available to all sub-blocks within it, but remember that (as mentioned in the Section called Language Structure " earlier in this chapter) variables declared within a sub-block are destroyed when that sub-block ends, and are not available for use by their parent blocks. The format for declaring a variable is shown in Example 11-11.

variable_name data_type
 [ := 
value
 ];

As you can see by Example 11-11, you declare a variable by providing its name and type (in that order), then end the declaration with a semicolon.

Example 11-12 shows the declaration of a variable of the INTEGER data type, a variable of the VARCHAR data type (the value in parentheses denotes that this variable type holds ten characters), and a variable of the FLOAT data type.

CREATE FUNCTION 
identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
     
     -- Declare an integer.
    subject_id INTEGER;
     
     -- Declare a variable length character.
    book_title VARCHAR(10);
      
      -- Declare a floating point number.
    book_price FLOAT;
  
  BEGIN
    
statements

  END;
' LANGUAGE 'plpgsql';

You may also specify additional options for a variable. Adding the CONSTANT keyword indicates that a variable will be created as a constant. Constants are discussed later in this section.

The NOT NULL keywords indicate that a variable cannot be set as NULL. A variable declared as NOT NULL will cause a run-time error if it is set to NULL within the code block. Due to the fact that all variables are set to NULL when declared without a default value, a default value must be provided for any variable that is declared as NOT NULL.

The DEFAULT keyword allows you to provide a default value for a variable. Alternatively, you can use the := operator without specifying the DEFAULT keyword, to the same effect.

The following illustrates the use of these options within a variable declaration:

  
variable_name
 [ CONSTANT ] 
data_type
 [ NOT NULL ] [ { DEFAULT | := } 
value
 ];

Example 11-13 shows the declaration of a constant variable with the default value of 5, the declaration of a variable with the value of 10 which cannot be set to NULL, and the declaration of a character with the default value of one a .

CREATE FUNCTION example_function () RETURNS text AS '
  DECLARE
    
    -- Declare a constant integer with a
    -- default value of 5.
    five CONSTANT INTEGER := 5;
    
    -- Declare an integer with a default
    -- value of 100 that cannot be NULL.
    ten INTEGER NOT NULL := 10;
    
    -- Declare a character with
    -- a default value of "a".
    letter CHAR DEFAULT ''a'';
  
  BEGIN
  return letter;
  END;
' LANGUAGE 'plpgsql';