PostgreSQL Tutorial: PL/pgSQL Row Types

August 4, 2023

Summary: in this tutorial, you will learn how to use the PL/pgSQL row types to declare row variables that hold a complete row of a result set.

Table of Contents

Introduction to PL/pgSQL row types

To store the whole row of a result set returned by the select into statement, you use the row-type variable or row variable.

You can declare a variable that has the same datatype as the datatype of the row in a table by using the following syntax:

row_variable table_name%ROWTYPE;
row_variable view_name%ROWTYPE;

To access the individual field of the row variable, you use the dot notation (.) like this:

row_variable.field_name

PL/pgSQL row types example

We’ll use the actor table from the sample database to show how row types work:

The following example shows the first name and last name of the actor id 10:

do $$
declare
   selected_actor actor%rowtype;
begin
   -- select actor with id 10   
   select * 
   from actor
   into selected_actor
   where actor_id = 10;

   -- show the number of actor
   raise notice 'The actor name is % %',
      selected_actor.first_name,
      selected_actor.last_name;
end; $$

How it works.

First, declare a row variable called selected_actor whose datatype is the same as the row in the actor table.
Second, assign the row whose value in the actor_id column is 10 to the selected_actor variable by using the select into statement.
Third, show the first name and last name of the selected actor by using the raise notice statement. It accessed the first_name and last_name fields using the dot notation.