PostgreSQL uses record type variables which simply act as placeholders for rows of a result set, similar to a row type variable. However, unlike row type variables, they do not have a predefined structure. Their structure is only determined after assigning a row to them. A record type variable also can change its structure after it is reassigned to another row.
Declaration:
We can declare a record type variable by simply using a variable name followed by the record keyword.
Syntax: variable_name record;
We can use the dot notation (.) to access any field from the record type variable. It is important to note that we must assign a record type variable before accessing its contents else, we will get an error.
Syntax: record_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 query shows how we can use record type variables in conjecture with the select into statement.
do $$ declare rec1 record; begin -- select the employee select employee_id,full_name,manager_id into rec1 from employees where employee_id = 13; raise notice '% - %(Manager id=%)', rec1.employee_id, rec1.full_name, rec1.manager_id; end; $$ language plpgsql;
Output:
In this example, we followed the following steps:
- We first declared the record type variable rec1 in the declaration area.
- We then used the select into the statement to select a row from the table employees into the variable rec1 whose employee id is 13.
- We then used the dot notation to access the contents of the variable rec1 and then print the information out
Example 2:
The following query shows how we can use record type variables in conjecture with the for loop statement.
do $$ declare rec1 record; begin for rec1 in select employee_id, full_name from employees where employee_id > 12 order by employee_id loop raise notice '% - %', rec1.employee_id, rec1.full_name; end loop; end; $$;
Output:
This example shows how we can reassign a record type variable.
In this example, we followed the following steps:
- We first declared a record type variable rec1 in the declaration area.
- We then used the for loop statement to iterate over the contents of the employees table. In each iteration, we assigned a new row to the record type variable rec1.
- We then used the dot notation to access the contents of the variable rec1 and then print the information out for each iteration.