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 $$ 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_actorwhose datatype is the same as the row in the
- Second, assign the row whose value in the
actor_idcolumn is 10 to the
selected_actorvariable by using the
- Third, show the first name and last name of the selected actor by using the
raise noticestatement. It accessed the
last_namefields using the dot notation.
- Use row type variables (
%ROWTYPE) to hold a row of a result set returned by the