PostgreSQL - Record type variable

In PostgreSQL, when working with PL/pgSQL (the procedural language for PostgreSQL), you can use a special kind of variable known as a record type variable. A record is a composite data type that can represent a row of a table or a row of a query result. It's a way to store multiple fields, possibly of different data types, into one PL/pgSQL variable.

Declaring a Record Type Variable

To declare a record type variable, you use the record keyword in a PL/pgSQL block:

DECLARE
    r record;

Using a Record Type Variable

You can use record type variables to hold the row returned by a SELECT statement. Here's a basic example:

DO $$ 
DECLARE 
    r record;
BEGIN 
    SELECT employee_id, first_name, last_name INTO r 
    FROM employees 
    WHERE employee_id = 1;

    RAISE NOTICE 'Employee %: % %', r.employee_id, r.first_name, r.last_name;
END $$;

In the above code:

We declare a record type variable named r.
We select a row from the employees table into the r variable.
We use RAISE NOTICE to print out the details from the record.

Looping Through Query Results

One common usage of record variables is looping through the result of a query:

DO $$ 
DECLARE 
    r record;
BEGIN 
    FOR r IN SELECT first_name, last_name FROM employees
    LOOP
        RAISE NOTICE 'Employee: % %', r.first_name, r.last_name;
    END LOOP;
END $$;

Notes:

Field Access: You can access fields of a record variable using dot notation, like r.field_name.
Type Stability: The actual structure of a record variable isn't fixed until you select or assign a row to it. Until then, it has no predefined fields. The fields come from the query result or the table row.
%TYPE and %ROWTYPE: While the record type is flexible, often it's beneficial to use the %ROWTYPE attribute for type safety. For instance, if you have a table named employees, you can declare a record that matches the structure of the employees table using employee_rec employees%ROWTYPE;.
NULL Handling: If a SELECT INTO statement doesn't return a row, the record variable will be NULL. You should be cautious of this and possibly check using IF NOT FOUND THEN ... END IF; after a SELECT INTO.

In conclusion, record type variables in PL/pgSQL are a powerful way to work with rows of data from tables or queries. They offer flexibility when you need to handle multiple columns from a result set in your procedural code.

How to declare and use record type variables in PostgreSQL:
- Declare a record type variable and assign values.
```
DECLARE
    my_record your_table%ROWTYPE;
BEGIN
    my_record := (1, 'John Doe');
END;
```
Assigning values to record type variables in PostgreSQL:
- Assign values to individual fields of a record type variable.
```
my_record.id := 2;
my_record.name := 'Jane Doe';
```

Record type variables in stored procedures and functions in PostgreSQL:

Use record type variables in stored procedures and functions.

CREATE OR REPLACE FUNCTION my_function()
RETURNS void AS $$
DECLARE
    my_record your_table%ROWTYPE;
BEGIN
    -- Your logic here
END;
$$ LANGUAGE plpgsql;

Returning record type variables from functions in PostgreSQL:

Return a record type variable from a function.

CREATE OR REPLACE FUNCTION get_user()
RETURNS your_table%ROWTYPE AS $$
DECLARE
    user_record your_table%ROWTYPE;
BEGIN
    -- Your logic here
    RETURN user_record;
END;
$$ LANGUAGE plpgsql;

Passing record type variables as parameters in PostgreSQL:

Pass record type variables as parameters to functions.

CREATE OR REPLACE FUNCTION update_user(user_record your_table%ROWTYPE)
RETURNS void AS $$
BEGIN
    -- Your update logic here
END;
$$ LANGUAGE plpgsql;

Destructuring record type variables in PostgreSQL:
- Extract values from a record type variable.
```
SELECT my_record.id, my_record.name
INTO my_id, my_name;
```