Open In App

PostgreSQL – Row Type Variables

Improve
Improve
Like Article
Like
Save
Share
Report

PostgreSQL uses the Row type variables to store a whole row of a result set returned by the select into statement.

Declaration :

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

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
);

Then we insert data into our employee table as follows:

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:

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 *  
  from employees
  into sel_employee
  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:

Example 2:

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  
  from employees
  into sel_employee
  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:


Last Updated : 01 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads