Variables are used to store and manipulate data. Variables are always defined in the declarations section of a block before it is used elsewhere in the block body. The declarations section is the portion beneath where you specified the keyword DECLARE . The possible data types a variable can be declared with are any valid SQL data type and a few other data types special to PL/pgSQL.

The following list some items that must be declared and examples of how to declare them:

An integer variable

      DECLARE 
      one INTEGER;

      BEGIN

      END;

Rows of a table

      DECLARE 
      product invoice%ROWTYPE;

      BEGIN

      END;
      

Data Records

      DECLARE 
      oneProd RECORD;

      BEGIN

      END;
      

As you may have noticed, each variable declared must be terminated with a semi-colon (;) and the variable name is listed first, then comes the data type of the variable.

The only variable that does not need to be declared is the incrementing variable used by the FOR Loop. This incrementing variable is set to increase a number by 1 every time the a statement loop is entered. See the Iteration section for more information on FOR Loops .

You can declare variables at the beginning of a block even though you may be planning to use the variable later in a sub block. This way, the variable can be visible to the entire function, instead of just to the sub-block. The range where the variable is visible is called the scope.

The next example shows how the scope is in violation. It first declares a phrase variable in a sub-block to hold a string. It then assigns Hello World! to the variable and exits the sub-block. Now that it is in the main block body, it tries to return the phrase . This will not work because the phrase is not visible outside of the sub-block.

Example 9-8. Scope Violation

CREATE FUNCTION scope_var() RETURNS text AS'
DECLARE
-- nothing is declared here

BEGIN

  DECLARE
     -- declares a string phrase
     phrase text;

  BEGIN
     -- assigns Hello World! to the phrase
     phrase = ''Hello World!'';

  -- end of the phrase's scope
  END;

-- this creates an ERROR!
return phrase;
END;
' LANGUAGE 'plpgsql';
  

To fix this problem, you can declare the phrase in the outer declarations section. Then add the Hello World! string to the phrase and display the result:

Example 9-9. Correct usage of Scope

CREATE FUNCTION scope_var() RETURNS text AS'
DECLARE
  -- declares a string phrase
  phrase text;
BEGIN

  DECLARE
  -- nothing is declared here   

  BEGIN
     -- assigns Hello World! to the phrase
     phrase = ''Hello World!'';

  -- jumps out of the sub block
  END;

-- displays the phrase 
return phrase;
END;
' LANGUAGE 'plpgsql';
   

	Escaping Single Quotes
	When the Hello World! string is put into the phrase variable, the string is inside a set of two single quotes. The first single quote escapes the inside pair of quotes. Be sure to remember this when you are dealing with strings in PL/pgSQL.

When this function is called with the SQL SELECT statement:

   SELECT scope_var();
   

It displays a temporary scope_var column with the phrase:

 
  scope_var
--------------
 Hello World!
(1 row)