Open In App

PostgreSQL – Record type variable

Improve
Improve
Like Article
Like
Save
Share
Report

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.

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