PL/pgSQL Variables

Summary: in this tutorial, you will learn about PL/pgSQL variables and various ways to declare them

Introduction to PL/pgSQL variables

In PL/pgSQL, variables are placeholders for storing data within a block. These variables can hold values of various types such as integers, booleans, text, and more.

Variables allow you to hold values for calculations, store query results, and so on.

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

Variables are scoped to the block in which they’re declared. It means that variables are accessible only within the block and any nested blocks.

The following illustrates the syntax of declaring a variable.

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

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 i you should use index or counter.
Second, associate a specific data type with the variable. The data type can be any valid data type such as integer, numeric, varchar, and char.
Third, optionally assign a default value to the variable. If you don’t do so, the initial value of the variable is NULL.

Please note that you can use either := or = assignment operator to set an initial value for a variable.

To assign a value to a variable, you can use the assignment operator (=) as follows:

variable_name = value;

Alternatively, you can use the := assignment operator:

variable_name := value;

Basic PL/pgSQL variable example

1) Declare variables

The following example shows how to declare and initialize variables:

do $$ 
declare
   counter    integer = 1;
   first_name varchar(50) = 'John';
   last_name  varchar(50) = 'Doe';
   payment    numeric(11,2) = 20.5;
begin 
   raise notice '% % % has been paid % USD', 
       counter, 
	   first_name, 
	   last_name, 
	   payment;
end $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  1 John Doe has been paid 20.50 USDCode language: CSS (css)

How it works.

First, declare four variables in the declaration part of the block:

The counter variable is an integer with an initial value of 1.
The first_name and last_name are varchar(50) with the initial values of 'John' and 'Doe' respectively.
The payment variable has the numeric type with the initial value 20.5.

Second, display the values of the variables using the raise notice statement.

2) Assign values to variables

The following example shows how to assign a value to a variable:

do $$
declare
	first_name VARCHAR(50);
begin
	first_name = split_part('John Doe',' ', 1);
	raise notice 'The first name is %', first_name;
end;
$$;Code language: PHP (php)

Output:

NOTICE:  The first name is John
DOCode language: PHP (php)

How it works.

First, declare a variable in the declaration block:

first_name VARCHAR(50);

Second, split a string literal into two parts using a space, return the first part, and assign it to the first_name variable:

first_name = split_part('John Doe',' ', 1);Code language: JavaScript (javascript)

Third, display the value of the first_name variable using the raise notice statement:

raise notice 'The first name is %', first_name;Code language: JavaScript (javascript)

Variable initialization timing

PostgreSQL evaluates the initial value of a variable and assigns it when the block is entered. For example:

do $$ 
declare
   created_at time = clock_timestamp();
begin 
   raise notice '%', created_at;
   perform pg_sleep(3);
   raise notice '%', created_at;
end $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

NOTICE:  14:23:33.064008
NOTICE:  14:23:33.064008Code language: Shell Session (shell)

In this example:

First, declare a variable created_at and initialize its value to the current time.
Second, display the variable.
Third, pause the execution for 3 seconds using the pg_sleep() function.
Finally, display the value of the created_at variable again.

The output indicates that the value of the created_at variable is only initialized once when the block is entered.

Copying data types

The %type provides the data type of a table column or another variable. Typically, you use the %type to declare a variable that holds a value from the database or another variable.

The following illustrates how to declare a variable with the data type of a table column:

variable_name table_name.column_name%type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following shows how to declare a variable with the data type of another variable:

variable_name variable%type;Code language: SQL (Structured Query Language) (sql)

We’ll use the following film table from the sample database:

This example uses the type-copying technique to declare variables that hold values that come from the film table:

do 
$$ 
declare
   film_title film.title%type;
   featured_title film_title%type;
begin 
   -- get title of the film id 100
   select title
   from film
   into film_title
   where film_id = 100;
   
   -- show the film title
   raise notice 'Film title id 100: %s', film_title;
end; $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  Film title id 100: Brooklyn Deserts
DOCode language: PHP (php)

In this example, we declare two variables:

The film_title variable has the same data type as the title column in the film table from the sample database.
The featured_title has the same data type as the data type of the film_title variable.

We use the select into statement to retrieve from the film_title column of the film table and assign it to the film_title variable.

Using the type-copying feature offers the following advantages:

First, you don’t need to know the type of column or reference being accessed.
Second, if the data type of the referenced column name (or variable) changes, you don’t need to change the block.

Variables in blocks and subblocks

When you declare a variable in a subblock with the same name as another variable in the outer block, the variable in the outer block is hidden within the subblock.

To access a variable in the outer block, you use the block label to qualify its name, as shown in the following example:

do 
$$ 
<<outer_block>>
declare
  counter integer := 0;
begin
   counter := counter + 1;
   raise notice 'The current value of the counter is %', counter;

   declare
       counter integer := 0;
   begin
       counter := counter + 10;
       raise notice 'Counter in the subblock is %', counter;
       raise notice 'Counter in the outer block is %', outer_block.counter;
   end;

   raise notice 'Counter in the outer block is %', counter;
   
end outer_block $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

NOTICE:  The current value of the counter is 1
NOTICE:  Counter in the subblock is 10
NOTICE:  Counter in the outer block is 1
NOTICE:  Counter in the outer block is 1Code language: HTTP (http)

In this example:

First, declare a variable named counter in the outer_block.
Next, declare a variable with the same name in the subblock.
Then, before entering into the subblock, the value of counter is one. Within the subblock, we increase the value of the counter variable to ten and print it out. Note that this change only affects the counter variable within the subblock.
After that, reference the counter variable in the outer block using the block label outer_block.counter.
Finally, display the value of the counter variable in the outer block, its value remains unchanged.

Summary

A variable is a named storage location with a data type that can hold a value.
PostgreSQL evaluates the default value of a variable and assigns it to the variable when it enters the block.
Declare variables and optionally an initial value to it in the declaration section of the block.

Was this tutorial helpful ?