Skip to content
Related Articles

Related Articles

PostgreSQL – Row Type Variables
  • Last Updated : 01 Feb, 2021
GeeksforGeeks - Summer Carnival Banner

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:


Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :