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

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire