PostgreSQL – Row Type Variables
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
Share your thoughts in the comments
Please Login to comment...