# PL/pgSQL Record Types Explained With Examples

> In PL/pgSQL, the record types are used to create the variables that can store a complete row/record of a result set.

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.

---
[View this page online](https://www.commandprompt.com/education/plpgsql-record-types-explained-with-examples/)