PL/pgSQL Record

Summary: in this tutorial, you’ll learn how to use the PL/pgSQL Record data type to represent a row of a query.

Overview of the PL/pgSQL record type #

The RECORD data type represents a single row of a query’s result set.

Here’s the syntax for declaring a RECORD variable:

DECLARE
    variable_name RECORD;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The RECORD type variable does not have a predefined structure. It inherits the structure of the row when you assign a row to it using the SELECT INTO or FOR statement.

The following shows how to select a row from a table and assign it to the record variable:

DO $$
DECLARE
    v_record RECORD;
BEGIN
    SELECT select_list INTO v_record 
    FROM table_name
    WHERE condition;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If a query returns multiple rows, you can use the FOR loop statement to assign each row of the result set to the record:

DO $$
DECLARE
    v_record RECORD;
BEGIN
    FOR v_record IN 
        SELECT select_list 
        FROM table_name 
        WHERE condition
    LOOP
        -- processing each row
    END LOOP;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It is important to note that RECORD is not a valid data type but a placeholder.

To access a field in a record, you use the dot notation syntax as follows:

v_record.field_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that you can only access a field of a record after assigning a row to it.

Basic PL/pgSQL record type example #

The following example defines a function called get_price() that returns the price of product specified by an id:

CREATE FUNCTION get_price(id INT) RETURNS DEC
AS
$$
DECLARE
    v_product RECORD;
BEGIN 
    SELECT product_name, price 
    INTO v_product
    FROM products
    WHERE product_id = id;
    
    IF FOUND THEN
        RETURN v_product.price;
    ELSE 
        RETURN NULL;
    END IF;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How it works

First, declare a record variable v_product with the type RECORD:

DECLARE v_product RECORD;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, retrieve the row from the products table with the product_id 1 and assign it to the v_product record variable:

SELECT product_name, price 
INTO v_product 
FROM products 
WHERE product_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, return the the product price if the product exists or NULL otherwise:

IF FOUND THEN
   RETURN v_product.price;
ELSE 
   RETURN NULL;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can call the get_price function to retrieve the price of the product with id 1:

SELECT get_price(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 get_price
-----------
    999.99Code language: plaintext (plaintext)

Using a RECORD variable with a FOR loop #

The following shows how to select the top three most expensive products and print them on the screen:

DO $$
DECLARE
    v_product RECORD;
BEGIN 
    
FOR v_product IN SELECT product_name, price 
    FROM products
    ORDER BY price DESC, product_name  LIMIT 3
LOOP    
        RAISE NOTICE '%: $%',  v_product.product_name, v_product.price;
END LOOP;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

First, declare a variable with the type RECORD in the declaration section:

DECLARE v_product RECORD;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, use a FOR statement to retrieve the top 3 most expensive products and assign each to the record variable:

FOR v_product IN SELECT product_name, price 
    FROM products
    ORDER BY price DESC, product_name  LIMIT 3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, display the product name and price in each iteration of the loop:

RAISE NOTICE '%: $%', v_product.product_name, v_product.price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Dynamic query execution with RECORD #

To execute a dynamic query, you use the EXECUTE statement:

EXECUTE  query;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the query is a literal string representing a query to execute.

When you execute a dynamic query, you only know the structure of the result set at runtime. In this case, a RECORD variable is ideal for handling the dynamic result.

Here’s the syntax for executing a dynamic query and assigning the row to a record:

EXECUTE query INTO record_variable;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example defines a function get_record that executes a dynamic query and returns a record.

CREATE FUNCTION get_record (query TEXT) 
RETURNS RECORD AS $$
DECLARE
    rec RECORD;
BEGIN
    EXECUTE query INTO rec;
    RETURN rec; 
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

To call the get_record function, you need to cast the record explicitly into a specific type:

SELECT
  *
FROM
  get_record (
    'SELECT product_name, price FROM products WHERE product_id = 1'
  ) AS product_info (product_name VARCHAR, price DEC);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    product_name    | price
--------------------+--------
 Samsung Galaxy S24 | 999.99Code language: plaintext (plaintext)

Summary #

• Use the PL/pgSQL RECORD data type to handle rows without a predefined structure.
• Use SELECT INTO and FOR loops to assign rows to a record.
• Use the dot notation (e.g., record_variable.field_name) to access individual fields of a record.
• Use RECORD variable for dynamic query where the row structure is known only at runtime.

Quiz #