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:
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
for statement assigns an actual row to it.
To access a field in the record, you use the dot notation (
.) syntax like this:
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
recin the declaration section.
- Second use the
select intostatement to select a row whose
film_idis 200 into the
- 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
- Second, use the
for loopstatement to fetch rows from the
filmtable (in the sample database). The
for loopstatement assigns the row that consists of
recvariable in each iteration.
- Third, show the contents of the fields of the record variable by using the dot notation (
- 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.