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: