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 Data Types section of Chapter 3, Understanding SQL. A brief list of supported data types located below.

• boolean

• char

• integer

• double precision

• date

• time

Assignment

Variable assignment is done with PL/pgSQL's assignment operator (:=), in the form of left_variable := right_variable , which assigns the value of the right variable to the left variable, or left_variable := expression, which assigns the left-hand variable the returned 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-11 shows 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 a SELECT INTO statement to assign variables the results of queries. This SELECT INTO is different than the SQL SELECT INTO, which assigns the results of a query to a new table (to assign the results of a query to a new table within PL/pgSQL, use the alternative SQL syntax CREATE TABLE AS SELECT). SELECT INTO is primarily used to assign row and record information to variables declared as %ROWTYPE or RECORD types. To use SELECT INTO with normal variable, the variable in question must be the same type as the column you reference in the SQL SELECT statement provided. Syntax of SELECT INTO statement is shown in Example 11-12.

CREATE FUNCTION identifier (arguments) RETURNS type AS' 
  DECLARE
    statement;
  BEGIN
    SELECT INTO target_variable select_statement; 
  END;
' LANGUAGE 'plpgsql';

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

CREATE FUNCTION get_customer_id (text,text) RETURNS text 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-14 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)

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 nullity 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-15 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-16 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 store information from the user that the function may require, which greatly extends 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. Arguments must follow the argument list defined when the function is first created. Example 11-17 first shows how a user would call a function that does not accept arguments, then how a user would call a function that does accept arguments. The example is valid because more than one function of a same name can exist, as long as they accept different arguments.

booktown=# SELECT get_author('Andrew');
[...]

booktown=# SELECT get_subject('Practical PostgreSQL');
[...] 

The get_author() and get_subject() functions will be discussed later in this chapter.

Each function argument that is received is incrementally assigned to an identifier that begins with the dollar sign ($) and is labeled with the argument number, beginning with the identifier $1 for the first argument. The maximum number of function arguments that will be processed is sixteen, so the argument identifiers can range from $1 to $16. Example 11-18 shows a function that doubles an integer argument variable that 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 through 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-19 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-20 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-21.

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

Returning Variables

All 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 functions, but will also often be located within an IF statement or other statement that directs the flow of the function. If a program'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 the last RETURN statement). The syntax of a RETURN statement is located in Example 11-22.

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. Use attributes to assign a variable either the type of a database object, with the %TYPE attribute, or the row structure of a row with the %ROWTYPE attribute. A variable should be declared with an attribute when it will be used during 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 (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 that follows structure of
     -- the authors table.
    found_author authors%ROWTYPE;

  BEGIN

      -- Retrieve a row of author information for
      -- the author whose first name matches
      -- the argument received by the function.
    SELECT INTO found_author * FROM authors WHERE first_name = f_name;

     -- Return the first
    RETURN found_author.first_name || '' '' || found_author.last_name;

  END;
' LANGUAGE 'plpgsql';

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

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 Language Structure section of 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-28.

variable_name data_type [ := value ]; 

As you can see by Example 11-28, you declare a variable by providing its name and type (in that order), then end it with a semi-colon.

Example 11-29 shows the declaration of a variable with the INTEGER data type, a variable with the VARCHAR data type (the parentheses denote that this variable type has ten elements), and a variable with 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 the variable. Adding the CONSTANT keyword indicates that the variable will be created as a constant. Constants are discussed later in this section. The NOT NULL keywords indicate that the 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 the variable (alternatively, you can provide the value without specifying this keyword, to the same effect). The use of these options within a variable declaration is shown in Example 11-30.

variable_name [ CONSTANT ] variable_type [ { DEFAULT } := variable_type ]; 

Example 11-31 shows the declaration of a constant variable with the default value of 5, the declaration of a constant variable with the value of 100, and the declaration of a constant character with the default value of one blank character (' ').

CREATE FUNCTION identifier (arguments) RETURNS type AS'
  DECLARE   
    
     -- Declare a constant integer with a
     -- default value of 5.  
    sort_head CONSTANT INTEGER := 5; 
    
     -- Declare a constant integer with a 
     -- default value of 100. 
    sort_tail CONSTANT INTEGER := 100; 
    
     -- Declare a constant character with
     -- a default value of one blank character. 
    space CONSTANT CHAR := '' '';

  BEGIN
    statement;
    [...]
  END;
' LANGUAGE 'plpgsql';   

Concatenation

Concatenation is the process of combining two (or more) strings together to produce another string. 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)

Alternatively, concatenated text can be assigned to a string variable.

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)

Renaming

Variable identifiers can be renamed using the RENAME keyword. Renaming a variable's identifier only alters the way the variable is referenced; you will not modify the value of a variable by renaming it. Variables declared with %TYPE and %ROWTYPE can also be renamed with the RENAME keyword.

The syntax to rename a variable or row is shown in Example 11-34.

RENAME old_identifier TO new_identifier; 

A basic example of renaming variables with the RENAME keyword is shown in Example 11-35. See the Triggers section of this chapter for more examples of using the RENAME keyword, as it is generally most useful when working with triggers that reference tables with the NEW and OLD keywords.

CREATE FUNCTION get_subject (text) RETURNS text AS '
  DECLARE
  
     -- Declare an alias for the function argument. 
    book_title ALIAS FOR $1; 
    
     -- Declare a text field to contain the subject
     -- when it is found. 
    found_subject TEXT; 
    
     -- Declare an integer to hold the requested subject
     -- id number when it is identified. 
    sub_id INTEGER;  
  
  BEGIN
  
     -- Retrieve the subject ID number of the book with a title that 
     --matches the title supplied by the user of the function and insert 
     -- it into sub_id. 
    SELECT INTO sub_id subject_id FROM books WHERE title = book_title; 
  
     -- Retrieve the name of the matching subject and insert it into 
     -- found_subject. 
    SELECT INTO found_subject subject FROM subjects WHERE id = sub_id; 
    
     -- Return the name of the found subject. 
    RETURN found_subject; 
END; 
' LANGUAGE 'plpgsql';