This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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:
DECLARE one INTEGER; BEGIN END;
DECLARE product invoice%ROWTYPE; BEGIN END;
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)
 
Continue to: