Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Using Variables

Variables are used within PL/pgSQL code to store modifiable data of an explicitly stated type. All variables that you will be using within a code block must be declared under the DECLARE keyword. If a variable is not initialized to a default value when it is declared, its value will default to the SQL NULL type.

Note: As you will read later on in the Section called Controlling Program Flow," there is a type of statement known as the FOR loop that initializes a variable used for iteration. The FOR loop's iteration variable does not have to be pre-declared in the DECLARE section for the block the loop is located within; hence, the FOR loop is the only exception to the rule that all PL/pgSQL variables must be declared at the beginning of the block they are located within.

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.

Example 11-11. Declaring a PL/pgSQL variable

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.

Example 11-12. Variable Declarations

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.

Example 11-13. Using variable declaration options

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

Warning

The RENAME keyword covered in online documentation for PL/pgSQL, which is intended to rename existing variables to new names, does not work at all in PL/pgSQL (as of PostgreSQL 7.1.x). The use of this keyword on an existing variable indiscriminately causes a parsing error. It is therefore not recommended, nor documented in this chapter.

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.

Example 11-14. Default value assignment

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.

Note: 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 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.

Example 11-15. Using the SELECT INTO statement

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.

Example 11-16. Result of the get_customer_id( ) function

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.

Example 11-17. Using SELECT INTO with multiple columns

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.

Example 11-18. Result of the get_customer_name( ) function

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.

Example 11-19. Using the FOUND boolean in get_customer_id( )

[...]
    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.

Example 11-20. Result of the new get_customer_id( ) function

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.

Example 11-21. Function call examples

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

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

Note: The get_author(text) and get_author(integer) functions are discussed later in this chapter.

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.

Example 11-22. Directly using argument variables

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.

Example 11-23. 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.

Example 11-24. Using PL/pgSQL aliases

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.

Example 11-25. Result of the triple_price( ) function

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.

Example 11-26. Syntax of the RETURN statement

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.

The %TYPE attribute

The %TYPE attribute is used to declare a variable with the data type of a referenced database object (most commonly a table column). The format for declaring a variable in this manner is shown in Example 11-27.

Example 11-27. Declaring a variable using %TYPE

variable_name table_name.column_name%TYPE

Example 11-28 shows the code for a function that uses %TYPE to store the last name of an author. This function uses string concatenation with the concatenation operator (||), which is documented in a later section. The use of the SELECT INTO statement was discussed earlier in this chapter.

Focus on the use of the %TYPE attribute in Example 11-28. Essentially, a variable is declared as being the same type as a column within the authors table. SELECT is then used to find a row with a first_name field that matches the name the user passed to the function. The SELECT statement retrieves the value of that row's last_name column and insert it into the l_name variable. An example of the user's input to the function is shown right after Example 11-28, in Example 11-29, and more examples of user input can be found later in this chapter.

Example 11-28. Using the %TYPE attribute

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

Example 11-29 shows the results of using the get_author() function.

Example 11-29. Results of the get_author( ) function

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

The %ROWTYPE Attribute

%ROWTYPE is used to declare a PL/pgSQL record variable with the same structure as the rows in a table you specify. It is similar to the RECORD data type, but a variable declared with %ROWTYPE will have the exact structure of a table's row, whereas a RECORD variable is not structured and will accept a row from any table.

Example 11-30 overloads the get_author() function that was created in Example 11-28 to accomplish a similar goal. Notice, though, that this new version of get_author() accepts an argument of type integer rather than text, and checks for the author by comparing their id against the passed integer argument.

Notice also that this function is implemented using a variable declared with %ROWTYPE. The use of %ROWTYPE to accomplish a simple task such as this may make it seem overly complicated, but as you learn more about PL/pgSQL, the importance of %ROWTYPE will become more apparent.

The use of the dot (.) within the found_author variable in Example 11-30 references a named field value in found_author.

Example 11-30. Using the %ROWTYPE attribute

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

Observe the use of the asterisk (*) for the column list in Example 11-30. Since found_author is declared with the %ROWTYPE attribute on the authors table, it is created with the same data structure as the authors table. The asterisk can therefore be used to populate the found_author variable with each column value selected from the SELECT INTO statement in Example 11-31.

Example 11-31. Results of the new get_author( ) function

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).

Example 11-32. Returning a concatenated string

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)

Example 11-33. Assigning a concatenated value to a string

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)

 
 
  Published courtesy of O'Reilly Design by Interspire