Using Variables

Data types

Variables in PL/pgSQL can be represented by any of SQL's standard data types, such as an INTEGER or CHAR. In addition to SQL data types, PL/pgSQL also provides the additional RECORD data type, which is designed to allow you to store row information without specifying the columns that will be supplied when data is inserted into the variable. More information on using RECORD data types is provided later in this chapter. For further information on standard SQL data types, see the Section called Data Types in Chapter 3" in Chapter 3; the following is a brief list of commonly used data types in PL/pgSQL:

• boolean

• text

• char

• integer

• double precision

• date

• time

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';

Assignment

Variable assignment is done with PL/pgSQL's assignment operator (:=), in the form of left_variable := right_variable, in which the value of the right variable is assigned to the left variable. Also valid is left_variable := expression, which assigns the left-hand variable the value of the expression on the right side of the assignment operator.

Variables can be assigned default values within the declaration section of a PL/pgSQL code block. This is known as default value assignment, and is done by using the assignment operator (:=) on the same line as the variable's declaration. This topic is discussed in more detail later in this section, but Example 11-14 provides a quick demonstration.

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    an_integer int4 := 10;
  BEGIN
    statement;
    [...]
  END;
' LANGUAGE 'plpgsql';

It is also possible to use a SELECT INTO statement to assign variables the results of queries. This use of SELECT INTO is different from the SQL command SELECT INTO, which assigns the results of a query to a new table.

SELECT INTO is primarily used to assign row and record information to variables declared as %ROWTYPE or RECORD types. To use SELECT INTO with a normal variable, the variable in question must be the same type as the column you reference in the SQL SELECT statement provided. The syntax of SELECT INTO statement is shown in the following syntax:

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    statement;
  BEGIN
    SELECT INTO target_variable [, ...] target_column [, ...] select_clauses;
  END;
' LANGUAGE 'plpgsql';

In this syntax, target_variable is the name of a variable that is being populated with values, and select_clauses consists of any supported SQL SELECT clauses that would ordinarily follow the target column list in a SELECT statement.

Example 11-15 shows a simple function that demonstrates the use of a SELECT INTO statement. The ALIAS keyword is described in the Section called Argument Variables," later in this chapter. See the Section called Controlling Program Flow" for examples of using SELECT INTO with RECORD and %ROWTYPE variables.

CREATE FUNCTION get_customer_id (text,text) RETURNS integer AS '
  DECLARE
    
    -- Declare aliases for user input.
    l_name ALIAS FOR $1;
    f_name ALIAS FOR $2;
    
    -- Declare a variable to hold the customer ID number.
    customer_id INTEGER;
  
  BEGIN
    
    -- Retrieve the customer ID number of the customer whose first and last
    --  name match the values supplied as function arguments.
    SELECT INTO customer_id id FROM customers
      WHERE last_name = l_name AND first_name = f_name;
    
    -- Return the ID number.
    RETURN customer_id;
  END;
' LANGUAGE 'plpgsql';

Example 11-16 shows the results of the get_customer_id() function when passed the arguments Jackson and Annie. The number returned is the correct ID number for Annie Jackson in the customers table.

booktown=# SELECT get_customer_id('Jackson','Annie');
 get_customer_id
-----------------
 107
(1 row)

If you wish to assign multiple column values to multiple variables, you may do so by using two comma-delimited groups of variable names and column names, separated from one another by white space. Example 11-17 creates essentially an inverse function to the get_customer_id() function created in Example 11-15.

CREATE FUNCTION get_customer_name (integer) RETURNS text AS '
  DECLARE
    
    -- Declare aliases for user input.
    customer_id ALIAS FOR $1;
    
    -- Declare variables to hold the customer name.
    customer_fname TEXT;
    customer_lname TEXT;
  
  BEGIN
    
    -- Retrieve the customer first and last name for the customer
    -- whose ID matches the value supplied as a function argument.
    SELECT INTO customer_fname, customer_lname
                first_name, last_name
                FROM customers WHERE id = customer_id;
    
    -- Return the name.
    RETURN customer_fname || '' '' || customer_lname;
  
  END;
' LANGUAGE 'plpgsql';

Example 11-18 shows the results of the get_customer_name() function, when passed an argument of 107.

booktown=# SELECT get_customer_name(107);
 get_customer_name
-------------------
 Annie Jackson
 (1 row)

Use the special FOUND Boolean variable directly after a SELECT INTO statement to check whether or not the statement successfully inserted a value into the specified variable. You can also use ISNULL or IS NULL to find out if the specified variable is NULL after being selected into (in most situations, this would mean the SELECT INTO statement failed).

FOUND, IS NULL, and ISNULL should be used within a conditional (IF/THEN) statement. PL/pgSQL's conditional statements are detailed in the "Controlling Program Flow" section of this chapter. Example 11-19 is a basic demonstration of how the FOUND Boolean could be used with the get_customer_id() function.

[...]
    SELECT INTO customer_id id FROM customers
          WHERE last_name = l_name AND first_name = f_name;
     
     -- If a match could not be found, return -1 (another function calling
     -- this function could then be made to interpret a -1 as an error.
    IF NOT FOUND THEN
      return -1;
    END IF;
[...]

Example 11-20 shows that get_customer_id( ) now returns a –1 value when passed the name of a non-existent customer.

booktown=# SELECT get_customer_id('Schmoe','Joe');
 get_customer_id
-----------------
 -1
(1 row)

Argument Variables

PL/pgSQL functions can accept argument variables of different types. Function arguments allow you to pass information from the user into the function that the function may require. Arguments greatly extend the possible uses of PL/pgSQL functions. User input generally provides a function with the data it will either operate on or use for operation. Users pass arguments to functions when the function is called by including them within parentheses, separated by commas.

Arguments must follow the argument list defined when the function is first created. Example 11-21 shows a pair of example function calls from psql.

booktown=# SELECT get_author('John');
  get_author
--------------
 John Worsley
(1 row)

booktown=# SELECT get_author(1111);
  get_author
--------------
 Ariel Denham
(1 row)

Each function argument that is received by a function is incrementally assigned to an identifier that begins with the dollar sign ($) and is labeled with the argument number. The identifier $1 is used for the first argument, $2 is used for the second argument, and so forth. The maximum number of function arguments that can be processed is sixteen, so the argument identifiers can range from $1 to $16. Example 11-22 shows a function that doubles an integer argument variable that is passed to it.

CREATE FUNCTION double_price (float) RETURNS float AS '
  DECLARE
  BEGIN
     
     -- Return the argument variable multiplied by two.
    return $1 * 2;
  
  END;
' LANGUAGE 'plpgsql';

Referencing arguments with the dollar sign and the argument's order number can become confusing in functions that accept a large number of arguments. To help in functions where the ability to better distinguish argument variables from one another is needed (or just when you wish to use a more meaningful name for an argument variable), PL/pgSQL allows you to create variable aliases.

Aliases are created with the ALIAS keyword and give you the ability to designate an alternate identifier to use when referencing argument variables. All aliases must be declared in the declaration section of a block before they can be used (just like normal variables). Example 11-23 shows the syntax of the ALIAS keyword.

CREATE FUNCTION function_identifier (arguments) RETURNS type AS '
  DECLARE
    identifier ALIAS FOR $1;
    identifier ALIAS FOR $2;
  BEGIN
    [...]
  END;
' LANGUAGE 'plpgsql';

Example 11-24 creates a simple function to demonstrate the use of aliases in a PL/pgSQL function. The triple_ price() function accepts a floating point number as the price and returns that number multiplied by three.

CREATE FUNCTION triple_price (float) RETURNS float AS '
  DECLARE
     
     -- Declare input_price as an alias for the argument variable
     -- normally referenced with the $1 identifier.
    input_price ALIAS FOR $1;
  
  BEGIN
     
     -- Return the input price multiplied by three.
    
    RETURN input_price * 3;
  
  END;
 ' LANGUAGE 'plpgsql';

Now, if we use the triple_ price function within a SQL SELECT statement in a client such as psql, we receive the results shown in Example 11-25.

booktown=# SELECT double_price(12.50);
 double_price
--------------
           25
(1 row)

Returning Variables

PL/pgSQL functions must return a value that matches the data type specified as their return type in the CREATE FUNCTION command that created them. Values are returned with a RETURN statement. A RETURN statement is typically located at the end of a function, but will also often be located within an IF statement or other statement that directs the flow of the function. If a function's RETURN statement is located within one of these control statements, you should still include a return statement at the end of the function (even if the function is designed to never reach that last RETURN statement). The syntax of a RETURN statement is shown in Example 11-26.

CREATE FUNCTION function_identifier (arguments) RETURNS type AS '
  DECLARE
    declaration;
    [...]
  BEGIN
    statement;
    [...]
    RETURN { variable_name | value }
  END;
' LANGUAGE 'plpgsql';

For a demonstration of the RETURN statement, examine any PL/pgSQL function example within this chapter.

Attributes

PL/pgSQL provides variable attributes to assist you in working with database objects. These attributes are %TYPE and %ROWTYPE. Use attributes to declare a variable to match the type of a database object (using the %TYPE attribute) or to match the row structure of a row (with the %ROWTYPE attribute). A variable should be declared using an attribute when it will be used within the code block to hold values taken from a database object. Knowledge of the database object's type is not required when using attributes to declare variables. If an object's type changes in the future, your variable's type will automatically change to that data type without any extra code.

variable_name table_name.column_name%TYPE

CREATE FUNCTION get_author (text) RETURNS text AS '
  DECLARE
      
      -- Declare an alias for the function argument,
      -- which should be the first name of an author.
     f_name ALIAS FOR $1;
       
       -- Declare a variable with the same type as
       -- the last_name field of the authors table.
     l_name authors.last_name%TYPE;
  
  BEGIN
      
      -- Retrieve the last name of an author from the
      -- authors table whose first name matches the
      -- argument received by the function, and
      -- insert it into the l_name variable.
     SELECT INTO l_name last_name FROM authors WHERE first_name = f_name;
       
       -- Return the first name and last name, separated
       -- by a space.
     return f_name || '' '' || l_name;
  
  END;
' LANGUAGE 'plpgsql';

booktown=# SELECT get_author('Andrew');
   get_author
-----------------
 Andrew Brookins
(1 row)

CREATE FUNCTION get_author (integer) RETURNS text AS '
  DECLARE
    
    -- Declare an alias for the function argument,
    -- which should be the id of the author.
    author_id ALIAS FOR $1;
    
    -- Declare a variable that uses the structure of
    -- the authors table.
    found_author authors%ROWTYPE;
  
  BEGIN
    
    -- Retrieve a row of author information for
    -- the author whose id number matches
    -- the argument received by the function.
    SELECT INTO found_author * FROM authors WHERE id = author_id;
    
    -- Return the first
    RETURN found_author.first_name || '' '' || found_author.last_name;
  
  END;
' LANGUAGE 'plpgsql';

booktown=# SELECT get_author(1212);
  get_author
--------------
 John Worsley
(1 row)

Concatenation

Concatenation is the process of combining two (or more) strings together to produce another string. It is a standard operation built into PostgreSQL, and may therefore be used directly on variables within a PL/pgSQL function. When working with several variables containing character data, it is an irreplaceable formatting tool.

Concatenation can only be used with character strings. Strings are concatenated by placing the concatenation operator (||) between two or more character strings (string literal or a character string variable) that you wish to be combined. This can be used to combine two strings together to form a compound word, and to combine multiple strings together to form complex character string combinations.

Concatenation can only be used in situations where your function requires a string value, such as when a string must be returned (as shown in Example 11-32), or when you are assigning a new value to a string variable (as shown in Example 11-33).

CREATE FUNCTION compound_word(text, text) RETURNS text AS '
  DECLARE
     
     -- Define aliases for function arguments.
    word1 ALIAS FOR $1;
    word2 ALIAS FOR $2;
  
  BEGIN
     
     -- Return the resulting joined words.
    RETURN word1 || word2;
  
  END;

' LANGUAGE 'plpgsql';

When the words break and fast are passed as arguments to the compound_word() function, the function returns breakfast as the concatenated string:

booktown=# SELECT compound_word('break', 'fast');
 compound_word
---------------
 breakfast
(1 row)

CREATE FUNCTION title_and_author (text, text) RETURNS text AS '
    DECLARE
        
        -- Declare aliases for the two function arguments.
       title ALIAS for $1;
       author ALIAS for $2;
        
        -- Declare a text variable to hold the string result
        -- of the concatenation.
       result text;
    
    BEGIN
        
        -- Combine the title variable and the author
        -- variable together, placing a comma and the
        -- word by between them.
       result := title || '', by '' || author;
        
        -- Return the resulting string.
       return result;
    
    END;
' language 'plpgsql';

If you pass the strings Practical PostgreSQL and Command Prompt, Inc. to the function created in Example 11-33, the function returns Practical PostgreSQL, by Command Prompt, Inc.:

booktown=# SELECT title_and_author('Practical PostgreSQL','Command Prompt, Inc.');
               title_and_author
-----------------------------------------------
 Practical PostgreSQL, by Command Prompt, Inc.
(1 row)