PL/pgSQL Row Types

Summary: In this tutorial, you’ll learn how to use PL/pgSQL row types to declare variables that can hold a table row.

Overview of PL/pgSQL Row Types #

A composite type is a type that contains a list of fields and their corresponding types. In PL/pgSQL, a variable of a composite type is called a row-type variable (or row variable).

Here’s the syntax for declaring a row variable:

variable_name composite_type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The row variable can hold an entire row of a SELECT statement or FOR query result if the query’s columns match the fields of the composite type.

PL/pgSQL allows you to declare a row variable with the same type as the row of a table with the following syntax:

variable_name table_name%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Besides a table name, you can use a view name as follows:

variable_name view_name%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

When you create a table, PostgreSQL implicitly creates a corresponding composite type with the same name. For example, PostgreSQL automatically creates the profiles composite type when you create the profiles table:

Therefore, the following row-type variable declarations are the same:

v_profile profiles;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

and

v_profile profiles%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL recommends using the %ROWTYPE syntax to make the code more portable, such as PL/SQL record type in Oracle Database.

Basic Example of PL/pgSQL Row Types #

The following defines a function called get_contact that accepts an id and returns the contact of a user from the profiles table:

CREATE FUNCTION get_contact (id INT) RETURNS TEXT AS 
$$
DECLARE 
    v_profile profiles%ROWTYPE;
BEGIN
   SELECT * INTO v_profile
   FROM profiles
   WHERE user_id = id;
   
   RETURN v_profile.first_name || '  ' || v_profile.last_name || ' <' || v_profile.work_phone || '>';
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How it works:

First, declare the variable v_profile with the row type of the profiles table:

DECLARE 
   v_profile profiles%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, select a row from the profiles table and assign it to the row type variable v_profile:

SELECT * INTO v_profile
FROM profiles
WHERE user_id = id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, concatenate the first name, last name, and work phone of the user into a single string and return it:

RETURN v_profile.first_name || '  ' || v_profile.last_name || ' <' || v_profile.work_phone || '>';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the get_contact() function to get the contact of the user id 1:

SELECT get_contact(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Returning a value with the row type #

The following statement create a function that returns a profile based on a user id:

CREATE FUNCTION find_profile_by_id(id INT)
RETURNS profiles
AS
$$
DECLARE
   v_profile profiles%ROWTYPE;
BEGIN
   SELECT * INTO v_profile
   FROM profiles
   WHERE user_id = id;
   
   RETURN v_profile;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

First, declare the variable v_profilewith the row type of the profiles table:

DECLARE 
   v_profile profiles%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, select a row from the profiles table and assign it to the row type variable v_profile:

SELECT * INTO v_profile 
FROM profiles
WHERE user_id = id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, return the row type variable:

RETURN v_profile;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the find_profile_by_id function to find the profile of a user by id:

SELECT * FROM find_profile_by_id(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 user_id | first_name | last_name |  work_phone  |  home_phone
---------+------------+-----------+--------------+--------------
       1 | John       | Doe       | 408-456-7890 | 408-111-2222Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PL/pgSQL row-type variables to store an entire table row in a variable.