PL/pgSQL Variables

Summary: In this tutorial, you’ll learn about PL/pgSQL variables and how to declare, assign, and use variables.

Overview of PL/pgSQL Variables #

In PL/pgSQL, variables are identifiers with immediate values of various SQL types such as integer, varchar, and boolean.

Before using variables, you must declare them in the declaration section of the block.

Declaring a Variable #

Here’s the syntax for declaring a variable:

variable_name data_type [=initial_value];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, provide a variable nam variable_name.
Second, specify the data type of value that the variable will hold. The data_type can be any SQL’s data type like INT, DECIMAL, VARCHAR, and TEXT.
Third, assign an optional initial value to the variable. The variable will use NULL by default if you don’t assign an initial value.

Besides the assignment operator, you can use the := operator to assign an initial value to a variable:

variable_name data_type [:=initial_value];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following declares a variable called total_quantity with the type INT and initial value 0 in the declaration section and displays its value in the body section:

DO
$$
DECLARE 
    total_quantity INT = 0;
BEGIN
     RAISE NOTICE 'Total quantity %', total_quantity;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  Total quantity 0Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Besides the = and := operators, you can use the DEFAULT keyword to specify an initial value of a variable:

DECLARE 
    variable_name data_type DEFAULT initial_value;Code language: PHP (php)

For example:

DECLARE 
    total_quantity INT DEFAULT 0;Code language: PHP (php)

After initialization, you can use the variable in the later initialization within the same block. For example:

DECLARE
    min_shipping_cost DEC = 5;
    net_price DEC = min_shipping_cost;Code language: PHP (php)

In this declaration, the initial value of the net_price is the value of the min_shipping_cost variable.

Assigning a Value to a Variable #

To assign a value to a variable, you can use the assignment operator:

variable_name = value;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Or you can use the := operator:

variable_name := value;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following shows how to assign 10 to the total_quantity variable:

DO
$$
DECLARE 
    total_quantity INT = 0;
BEGIN
     total_quantity = 10;
     RAISE NOTICE 'Total quantity %', total_quantity;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  Total quantity 10Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Variable Scopes #

The scope of a variable specifies where in the code you can reference it.

In PL/pgSQL, the scope of a variable is within the block and nested blocks of the block where you declare it: