Summary: in this tutorial, we will show you how to declare PL/pgSQL variables using various techniques.
A PL/pgSQL variable is a meaningful name for a memory location. A variable holds a value that can be changed through the block or function. A variable is always associated with a particular data type.
Before using a variable, you must declare it in the declaration section of the PL/pgSQL block. The following illustrates the syntax of declaring a variable.
variable_name data_type [:= expression];
In this syntax:
- First, specify the name of the variable. It is a good practice to assign a meaningful name to a variable. For example, instead of naming a variable
iyou should use
- Second, associate a specific data type with the variable. The data type can be any valid PostgreSQL data type such as INTEGER, NUMERIC, VARCHAR and CHAR.
- Third, optionally assign a default value to a variable. If you don’t, the initial value of the variable is initialized to
The following example illustrates how to declare and initialize various variables:
counter INTEGER := 1;
first_name VARCHAR(50) := 'John';
last_name VARCHAR(50) := 'Doe';
payment NUMERIC(11,2) := 20.5;
RAISE NOTICE '% % % has been paid % USD', counter, first_name, last_name, payment;
counter variable is an integer that is initialized to 1
VARCHAR, 50 characters long, and are initialized to
Doe literal strings.
payment is a number with two decimals and is initialized to
Notice that PostgreSQL evaluates the default values and set them for the variables when the block is entered. See the following example:
created_at time := NOW();
RAISE NOTICE '%', created_at;
RAISE NOTICE '%', created_at;
Here is the output:
In this example:
- First, declared a variable whose default value is initialized to the current time.
- Second, printed out the value of the variable and paused the execution in 10 seconds
- Third, printed out the value of the created_at variable again.
As shown clearly from the output, the value of the
created_at is only initialized once when the block is entered.
Copying data types
PostgreSQL enables you to define a variable with a data type that references to the data type of a column of a table or the data type of another variable:
For example, you can define a variable named
city_name with the same data type as name the
name column of the
city table as follows:
city_name city.name%TYPE := 'San Francisco';
By using copying type feature, you receive the following advantages:
- First, you don’t need to care about the data type of the column. You declare a variable to just hold the values of that column in a query.
- Second, when the data type of the column changes, you don’t need to change the variable declaration in the function to adapt to the new changes.
- Third, you can refer the type of variables to data type of function arguments to create polymorphic functions since the type of internal variables can change from one call to the next.
Assigning aliases to variables
PostgreSQL allows you to define an alias for any variable as follows:
new_name ALIAS FOR old_name;
The aliases are used mainly in a trigger procedure to assign more meaningful names for variables that have predetermined names e.g.,
In this tutorial, you have learned how to declare variables using the various technologies in PL/pgSQL.