The term PL/pgSQL refers to the Procedural Language/Postgres. In PL/pgSQL, the row-type variables aka row variables are used to store a complete record of a result set into a specific variable. Although, the row variables keep the whole row, however, an individual field of a specific row-type variable can be accessed using the dot syntax.

This post explains how to assign a complete row to a variable in Postgres using the row-type variables.

PL/pgSQL Row Types - Assign Complete Row to a Variable in Postgres

To work with row variables, first, you must learn how to declare them. A row variable can hold a row returned by a Postgres table or a view. Here, is the basic syntax that allows you to declare the row variables:

row_var_name tab_name%ROWTYPE;

row_var_name view_name%ROWTYPE;

In this syntax:

- “row_var_name” represents the variable’s name.
- “tab_name” and “view_name” represent the targeted table or view.
- The “tab_name” and “view_name” must be followed by the “%ROWTYPE” to declare a row variable.

Use the following dot syntax to access a specific field of a row-type variable.

row_var_name.field_name;

Let’s learn the row-type variables practically.

Example 1: How to Assign a Complete Row to a Variable in Postgres?

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

SELECT * FROM emp_bio;

Let's declare a row-type variable and assigned it a complete 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 row-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 Row-Type Variable in Postgres?

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

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

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

Conclusion

In PL/pgSQL, the row-type variables aka row variables are used to store a complete record of a result set into a specific variable. A row-type variable can be declared with the same data type as the table’s selected row by using the “row_var_name tab_name%ROWTYPE” syntax. The dot syntax is used to access an individual field of a row-type variable. This post explained how to declare, assign, and access a row-type variable in Postgres using suitable examples.