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;