Practical PostgreSQL

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 titled 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 titled Data Types in Chapter 3; the following is a brief list of commonly used data types in PL/pgSQL:

• boolean

• text ...