August 4, 2023
Summary: in this tutorial, you will learn about the PL/pgSQL record types that allow you to define variables that can hold a single row from a result set.
Introduction to PL/pgSQL record types
PostgreSQL provides a “type” called the record
that is similar to the row-type.
To declare a record
variable, you use a variable name followed by the record
keyword like this:
variable_name record;
A record
variable is similar to a row-type variable. It can hold only one row of a result set.
Unlike a row-type variable, a record
variable does not have a predefined structure. The structure of a record
variable is determined when the select
or for
statement assigns an actual row to it.
To access a field in the record, you use the dot notation (.
) syntax like this:
record_variable.field_name;
If you attempt to access a field in a record variable before it is assigned, you will get an error.
In fact, a record is not a true data type. It is just a placeholder. Also, a record variable can change its structure when you reassign it.
PL/pgSQL record examples
Let’s take some examples of using the record variables.
1) Using record with the select into statement
The following example illustrates how to use the record variable with the select into
statement:
do
$$
declare
rec record;
begin
-- select the film
select film_id, title, length
into rec
from film
where film_id = 200;
raise notice '% % %', rec.film_id, rec.title, rec.length;
end;
$$
language plpgsql;
How it works.
- First, declare a record variable called
rec
in the declaration section. - Second use the
select into
statement to select a row whosefilm_id
is 200 into therec
variable - Third, print out the information of the film via the record variable.
2) Using record variables in the for loop statement
The following shows how to use a record variable in a for loop
statement:
do
$$
declare
rec record;
begin
for rec in select title, length
from film
where length > 50
order by length
loop
raise notice '% (%)', rec.title, rec.length;
end loop;
end;
$$
Here is the parial output:
NOTICE: Hall Cassidy (51)
NOTICE: Champion Flatliners (51)
NOTICE: Deep Crusade (51)
NOTICE: Simon North (51)
NOTICE: English Bulworth (51)
...
Note that you will learn more about the for loop
statement in the for loop tutorial.
How it works:
- First, declare a variable named r with the type
record
. - Second, use the
for loop
statement to fetch rows from thefilm
table (in the sample database). Thefor loop
statement assigns the row that consists oftitle
andlength
to therec
variable in each iteration. - Third, show the contents of the fields of the record variable by using the dot notation (
rec.title
andrec.length
)
Summary
- A record is a placeholder that can hold a single row of a result set.
- A record has not predefined structure like a row variable. Its structure is determined when you assign a row to it.