PostgreSQL – Record Type Variable

In PostgreSQL, record-type variables provide a dynamic and flexible way to handle result sets that don’t have a predefined structure. Unlike row-type variables, PostgreSQL record-type variables can change their structure after being assigned a new row, making them highly versatile for dynamic SQL operations.

This article will explore how to declare and use record-type variables in PostgreSQL, including practical examples with the SELECT INTO statement and FOR loop.

Declaring a Record Type Variable in PostgreSQL

Let us get a better understanding of the Record type variable in PostgreSQL from this article. We can declare a record-type variable by simply using a variable name followed by the record keyword. A record-type variable can hold one row of data from a result set, and we can access its fields using dot notation. If we try to access a field before assigning a row to the record variable, we will encounter an error.

Syntax:

variable_name record;

Syntax for Accessing Fields in Record Type Variable

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;

Example of Record Type Variable in PostgreSQL

Let us take a look at some of the examples of Record Type Variables in PostgreSQL to better understand the concept. But first, we create a sample table using the below commands to perform examples:

Query:

CREATE TABLE employees (
employee_id serial PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);
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);

Output

employees table

Example 1: Using Record Type Variables with SELECT INTO

The following query demonstrates how to use record type variables in conjunction with the SELECT INTO statement. which allows us to store a row directly into a record type variable from a query result. This method is useful for fetching specific rows and processing them efficiently.

Query:

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

PostgreSQL Record type variable Example1

Explanation:

1. Declaration: Declare the record type variable rec1.
2. Assignment: Use the SELECT INTO statement to select a row from the ‘employees' table into rec1 where ‘employee_id' is 13.
3. Accessing Fields: Use dot notation to access the contents of ‘rec1' and print the information.

Example 2: Using Record Type Variables with FOR Loop

The following query shows how we can use record type variables in conjecture with the FOR loop statement. This allows iterating over multiple rows from a query result, with each row assigned to the record variable during each iteration, enabling dynamic row processing.

Query:

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

PostgreSQL Record type variable Example2

Explanation:

1. Declaration: Declare the record type variable ‘rec1'.
2. FOR Loop: Use the FOR loop statement to iterate over the ‘employees' table. In each iteration, assign a new row to ‘rec1'.
3. Accessing Fields: Use dot notation to access the contents of ‘rec1' and print the information for each iteration.

Important Points About PostgreSQL Record Type Variable

• Dynamic Structure: Record type variables do not have a predefined structure. Their structure is determined dynamically based on the row assigned to them.
• Dot Notation: We can access fields within a record type variable using dot (.) notation.
• Assignment Before Access: Ensure that the fields accessed within a record type variable are not NULL to avoid runtime errors.
• Local Scope: Record type variables are local to the PL/pgSQL block in which they are declared.
• Less Type-Safe: While record type variables offer flexibility, they are less type-safe compared to row type variables.

Conclusion

In this article, we have explained the dynamic nature of PostgreSQL record type variables, their declaration, and their usage in both SELECT INTO and FOR loops. These variables provide a flexible way to handle result sets where the structure is not predefined.

By using record type variables, we can process rows dynamically and efficiently in PL/pgSQL. Whether we are working with PostgreSQL SELECT INTO record or PostgreSQL record type with FOR loop, understanding how to use these variables is essential for dynamic SQL operations.