In PL/pgSQL aka Procedural Language/Postgres, the record types are used to create the variables that can store a complete row/record of a result set. The record is not a proper data type, instead, it’s just a variable/placeholder. These variables are the same as the row-type variables; the only difference is that the record variables don’t have a predefined structure while the row-type variable does.

This post explains how to declare, assign and access a record variable in Postgres.

PL/pgSQL Record Types

A record variable can hold a single row returned by a Postgres table or a view. A record-type variable's structure is defined when FOR and SELECT statements assign an actual record/row to such variables. Use the following syntax to declare the record variables:

var_name RECORD;

To access a specific field of a record variable, use the following dot syntax:

var_name.field_name;

Accessing a record-type field prior to its declaration causes an error. A record variable can be re-assigned and its structure changes when you re-assign it.

Let’s learn the row-type variables practically.

Example 1: How to Assign a Complete Row to a Record Type Using SELECT INTO Statement?

In this example, we will utilize a sample table named “emp_bio” whose details are shown in the below snippet:

SELECT * FROM emp_bio;

Let's declare a record-type variable and assigned it a single row:

DO $$
DECLARE emp_info emp_bio%ROWTYPE;
BEGIN
SELECT *
INTO emp_info
FROM emp_bio
WHERE e_id = 3;
RAISE NOTICE 'Employee INFO: %', emp_info;
END;
$$

In the above code:

- A record-type variable named “emp_info” is declared.
- The INTO keyword is used to specify the selected row into the “emp_info” variable.
- The FROM clause keeps the name of the targeted table, i.e., “emp_bio”.
- The WHERE clause defines the selection criteria.
- The “RAISE NOTICE” is used to display the variable’s value.

The output shows the complete row that is stored in the “emp_info” variable.

Example 2: How to Access a Specific Field of a Record-Type Variable in Postgres?

In the following code snippet, we will utilize the dot syntax to access an individual field of the record-type variable:

DO $$
DECLARE emp_info RECORD;
BEGIN
SELECT *
INTO emp_info
FROM emp_bio
WHERE e_id = 3;
RAISE NOTICE 'Employee Salary: %', emp_info.emp_sal;
END;
$$

The output confirmed that an individual field of the record-type variable has been accessed successfully.

Example 3: How to Assign a Complete Row to a Record Type Using For Loop?

In the below code snippet, we will explain the working of the record variables in the for loop:

DO $$
DECLARE emp_info RECORD;
BEGIN
For emp_info IN SELECT emp_name, emp_sal
FROM emp_bio
WHERE e_id >= 3
LOOP
RAISE NOTICE 'Name: %, Salary: %', emp_info.emp_name, 
emp_info.emp_sal;
END LOOP;
END;
$$

In this example:

- A record-type variable named “emp_info” is declared.
- The for loop is used to get the employee's name and salary whose id is greater than or equal to 3.
- The dot syntax is utilized to access the individual fields of the record-type variable.

This is how you can use the RECORD variable in the for-loop.

Conclusion

In PL/pgSQL, the record types are used to create the variables that can store a complete row/record of a result set. The record is not a proper data type, instead, it’s just a variable or a placeholder that holds a single row returned by a Postgres table. These variables are the same as the row-type variables; the only difference is that the record variables don’t have a predefined structure while the row-type variable does. This post presented a detailed guide on record types using practical examples.