PL/pgSQL – Row Type Variables
In PostgreSQL’s PL/pgSQL language, you can use a row type variable to store and manipulate rows of data from database tables or other composite types. A row type variable is essentially a container that can hold an entire row of data, preserving the structure of the row, including the column names and data types.
Here’s how you declare and use a row type variable in PL/pgSQL:
DECLARE variable_name table_name%ROWTYPE;variable_nameis the name you want to give to the row type variable.table_nameis the name of the table or composite type whose structure you want to mimic in the row type variable.
Here’s an example that demonstrates the use of a row type variable. Consider a table named “employees” with columns “id,” “name,” and “salary.” You can create a row type variable to hold a row of data from this table:
DECLAREemp_record employees%ROWTYPE;BEGIN-- Assign a row from the "employees" table to the variableSELECT * INTO emp_record FROM employees WHERE id = 1;-- Access individual fields within the row variableRAISE NOTICE 'Employee ID: %', emp_record.id;RAISE NOTICE 'Employee Name: %', emp_record.name;RAISE NOTICE 'Employee Salary: %', emp_record.salary;END;
In this example:
- We declare a row type variable
emp_recordwith the same structure as the “employees” table. - We use a
SELECTstatement with theINTOclause to retrieve a row from the “employees” table and assign it to theemp_recordvariable based on a condition (in this case, whereidis 1). - We then access individual fields within the
emp_recordvariable to retrieve and display specific data from the row.
Row type variables are particularly useful when you need to work with entire rows of data, such as when querying a table or returning rows from a function. They allow you to maintain the structure of the row, making it easier to interact with the data contained within it.