This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Variables are the names given to objects which have a defined data type. The possible variable data types are any valid SQL data type, such as integer, date, time... etc. Refer to the data types table in the Understanding SQL chapter for more details on valid SQL data types.
This is an example which declares a variable for the author identification number and their firstname:
Example 9-10. Declaring a Variable in PL/pgSQL
DECLARE -- defines a variable to hold -- a numeric author identification number author_id INTEGER; -- defines a variable to hold the first name firstname TEXT; BEGIN -- instructions statements ; END;
Sometimes, after having declared a variable, you want to assign a different default value to variables and constants. The syntax is:
name [CONSTANT] type [NOT NULL] [{DEFAULT |:=}value];
The only situation that requires you to assign a default value is when the variable or constant is defined as NOT NULL . Remember, the default value is NULL . When a NOT NULL is specified, the default value is no longer valid. You must assign a new default value for the variable or constant.
These are some examples of assigning a default value to the variables, author_id and firstname :
Example 9-11. Assigning Default Values in PL/pgSQL
DECLARE -- assigns number 212 to the author_id variable author_id INTEGER := 212; -- assigns the name Suess to the firstname variable firstname TEXT := "Suess"; BEGIN statements ; END;
 
Continue to: