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)