PostgreSQL – Row Type Variables

Last Updated : 23 Aug, 2024

In PostgreSQL, row type variables are handy when you need to store a whole row of data returned by a query. They are particularly useful when dealing with SELECT INTO statements where the result contains multiple columns from a table or view. This feature simplifies handling data from complex queries and makes your PL/pgSQL code more readable and maintainable.

In this article, we’ll learn to declare and use row type variables in PostgreSQL, access specific fields within them using dot notation, and provide practical examples to help you understand their application.

What are Row Type Variables in PostgreSQL?

Row type variables in PostgreSQL allow you to store an entire row from a table or view in a single variable. The row is stored as a composite data type, which includes multiple columns that you can access individually.

These variables are declared using the ‘%ROWTYPE’ attribute, which automatically matches the structure of the row to the table or view you reference. This feature is beneficial when working with complex queries that return multiple columns, allowing you to handle the data more flexibly.

How to Declare Row Type Variables

We can declare a row type variable that has the same data type as the corresponding datatype in the row of the table or the view using the following syntax.

Syntax: row_variable table_name/view_name %ROWTYPE;

We can use the dot notation (.) to access any field from the row variable.

Syntax: row_variable.field_name

PostgreSQL Row Type Variables Examples

First, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (
 employee_id serial PRIMARY KEY,
 full_name VARCHAR NOT NULL,
 manager_id INT
);
INSERT INTO employees (
 employee_id,
 full_name,
 manager_id
)
VALUES
 (1, 'M.S Dhoni', NULL),
 (2, 'Sachin Tendulkar', 1),
 (3, 'R. Sharma', 1),
 (4, 'S. Raina', 1),
 (5, 'B. Kumar', 1),
 (6, 'Y. Singh', 2),
 (7, 'Virender Sehwag ', 2),
 (8, 'Ajinkya Rahane', 2),
 (9, 'Shikhar Dhawan', 2),
 (10, 'Mohammed Shami', 3),
 (11, 'Shreyas Iyer', 3),
 (12, 'Mayank Agarwal', 3),
 (13, 'K. L. Rahul', 3),
 (14, 'Hardik Pandya', 4),
 (15, 'Dinesh Karthik', 4),
 (16, 'Jasprit Bumrah', 7),
 (17, 'Kuldeep Yadav', 7),
 (18, 'Yuzvendra Chahal', 8),
 (19, 'Rishabh Pant', 8),
 (20, 'Sanju Samson', 8);

The table is:

Example 1: Selecting a Single Row with All Columns

The following will help create a row type variable sel_employee from the table employees

DO $$
DECLARE
  sel_employee employees%ROWTYPE;
BEGIN
  -- Select employee with ID 6
  SELECT * INTO sel_employee
  FROM employees
  WHERE employee_id = 6;
  
  RAISE NOTICE 'The employee name is % and the manager ID is %',
    sel_employee.full_name,
    sel_employee.manager_id;
END $$;

Output:

Explanation:

Row Type Variable: The ‘sel_employee’ variable can store an entire row from the employees table.
SELECT INTO Statement: The query selects all columns of the row where ‘employee_id = 6′ and stores it into sel_employee.
RAISE NOTICE: This statement outputs the employee’s name and manager ID, demonstrating how to access individual fields.

Example 2: Selecting a Single Row with Specific Columns

The following will help create a row type variable ‘sel_employee’ from the table employees with 2 columns ‘employee_id’ and ‘full_name’.

DO $$
DECLARE
  sel_employee employees%ROWTYPE;
BEGIN
  -- Select employee with ID 12
  SELECT employee_id, full_name  
  INTO sel_employee
  FROM employees
  WHERE employee_id = 12;
  
  RAISE NOTICE 'The employee name is % and the length of the name is %',
    sel_employee.full_name,
    LENGTH(sel_employee.full_name);
END $$;

Output:

Explanation:

Selecting Specific Columns: The query selects only ‘employee_id’ and ‘full_name’, making the row type variable more efficient for specific use cases.
RAISE NOTICE: The output message shows the selected employee’s name and the length of the name using the ‘LENGTH()’ function.

Important Points About PostgreSQL Row Type Variables

Always use the %ROWTYPE attribute when dealing with tables whose structure may change over time, as it automatically adapts to any schema changes.
Use dot notation for easy access to individual fields, improving code readability and maintainability.
Row type variables are excellent for batch processing when you need to fetch and process multiple rows iteratively.