PostgreSQL - Row type variables

In PostgreSQL, row-type variables allow you to work with complete table rows or rows of composite types. They can be especially handy within PL/pgSQL blocks, where you might want to process or manipulate entire rows in a loop or under certain conditions.

Declaration:

You can declare a row-type variable in a PL/pgSQL block by referencing a table or composite type's name as the datatype.

For example, if you have a table named employees, you can declare a variable to hold rows from that table like this:

DECLARE
    emp_row employees%ROWTYPE;

Usage:

Once declared, you can use row-type variables to hold complete rows of data. Here are some common usages:

1. Selecting into a Row-type variable:

BEGIN
    SELECT * INTO emp_row FROM employees WHERE emp_id = 1;
    RAISE NOTICE 'Employee Name: %', emp_row.emp_name;
END;

2. Looping through a result set:

Using a FOR loop, you can iterate through a result set:

DECLARE
    emp_row employees%ROWTYPE;
BEGIN
    FOR emp_row IN SELECT * FROM employees WHERE department = 'HR'
    LOOP
        RAISE NOTICE 'Employee Name: %', emp_row.emp_name;
    END LOOP;
END;

3. Using with RETURN in functions:

If you're writing a PL/pgSQL function that's supposed to return a set of rows from a table, row-type variables can be useful:

CREATE OR REPLACE FUNCTION get_hr_employees() RETURNS SETOF employees AS $$
DECLARE
    emp_row employees%ROWTYPE;
BEGIN
    FOR emp_row IN SELECT * FROM employees WHERE department = 'HR'
    LOOP
        RETURN NEXT emp_row;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

Composite Types:

Apart from tables, PostgreSQL allows you to create composite types. You can declare row-type variables based on these composite types in the same way as you would for tables:

CREATE TYPE comp_type AS (f1 integer, f2 text);

DECLARE
    v_row comp_type;

Accessing Fields:

Once you've stored data in a row-type variable, you can access its fields using the dot notation:

RAISE NOTICE 'Employee ID: %, Employee Name: %', emp_row.emp_id, emp_row.emp_name;

Notes:

If you only need a few fields from a table, it's often more efficient to declare individual variables for each field instead of using a row-type variable.
Remember that when using SELECT INTO with a row-type variable, if no rows match the query, the variable will not be assigned and will remain NULL. You should check for this case before trying to access the variable's fields.

In summary, row-type variables in PL/pgSQL offer a powerful way to work with complete rows of data, making it easier to write functions and procedures that process or generate sets of rows.

How to declare and use row type variables in PostgreSQL: Row type variables are declared using the ROW keyword and can hold an entire row of a table.
```
DECLARE
   your_row_variable your_table%ROWTYPE;
```

Assigning values to row type variables in PostgreSQL:

your_row_variable := (1, 'John Doe', 'example@email.com');

Row type variables in stored procedures and functions in PostgreSQL:

CREATE OR REPLACE FUNCTION your_function()
RETURNS void AS $$
DECLARE
   your_row_variable your_table%ROWTYPE;
BEGIN
   -- Use your_row_variable
END;
$$ LANGUAGE plpgsql;

Returning row type variables from functions in PostgreSQL:

CREATE OR REPLACE FUNCTION get_employee()
RETURNS your_table%ROWTYPE AS $$
DECLARE
   employee_record your_table%ROWTYPE;
BEGIN
   -- Assign values to employee_record
   RETURN employee_record;
END;
$$ LANGUAGE plpgsql;

Updating row type variables in PostgreSQL:

your_row_variable.column_name := new_value;

Passing row type variables as parameters in PostgreSQL:

CREATE OR REPLACE FUNCTION update_employee(employee_data your_table%ROWTYPE)
RETURNS void AS $$
BEGIN
   -- Use employee_data
END;
$$ LANGUAGE plpgsql;

Destructuring row type variables in PostgreSQL:

SELECT * INTO your_variable1, your_variable2, your_variable3
FROM your_row_variable;