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.

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:


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 $$
   selected_actor actor%rowtype;
   -- 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 % %',
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.


  • Use row type variables (%ROWTYPE) to hold a row of a result set returned by the select into statement.
comments powered by Disqus