{"id":1609,"date":"2025-01-11T13:49:06","date_gmt":"2025-01-11T06:49:06","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1609"},"modified":"2025-01-11T21:03:31","modified_gmt":"2025-01-11T14:03:31","slug":"plpgsql-record","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-record\/","title":{"rendered":"PL\/pgSQL Record"},"content":{"rendered":"\n

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

Overview of the PL\/pgSQL record type #<\/a><\/h2>\n\n\n\n

The RECORD<\/code> data type represents a single row of a query’s result set.<\/p>\n\n\n\n

Here’s the syntax for declaring a RECORD<\/code> variable<\/a>:<\/p>\n\n\n

DECLARE<\/span>\n\u00a0 \u00a0\u00a0variable_name RECORD<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows how to select a row from a table<\/a> and assign it to the record variable:<\/p>\n\n\n
DO<\/span> $$\nDECLARE<\/span>\n    v_record RECORD<\/span>;\nBEGIN<\/span>\n    SELECT<\/span> select_list INTO<\/span> v_record \n    FROM<\/span> table_name<\/span>\n    WHERE<\/span> condition;\nEND<\/span>;\n$$<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If a query returns multiple rows, you can use the FOR<\/code> loop statement to assign each row of the result set to the record:<\/p>\n\n\n
DO<\/span> $$\nDECLARE<\/span>\n    v_record RECORD<\/span>;\nBEGIN<\/span>\n    FOR<\/span> v_record IN<\/span> \n        SELECT<\/span> select_list \n        FROM<\/span> table_name<\/span> \n        WHERE<\/span> condition\n    LOOP<\/span>\n        -- processing each row<\/span>\n    END<\/span> LOOP<\/span>;\nEND<\/span>;\n$$<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
It is important to note that RECORD<\/code> is not a valid data type but a placeholder.<\/p>\n\n\n\n
To access a field in a record, you use the dot notation syntax as follows:<\/p>\n\n\n
v_record.field_name<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you can only access a field of a record after assigning a row to it.<\/p>\n\n\n\n
Basic PL\/pgSQL record type example #<\/a><\/h2>\n\n\n\n
The following example defines a function called get_price()<\/code> that returns the price of  product specified by an id:<\/p>\n\n\n
CREATE<\/span> FUNCTION<\/span> get_price(id INT<\/span>) RETURNS<\/span> DEC<\/span>\nAS<\/span>\n$$\nDECLARE<\/span>\n    v_product RECORD<\/span>;\nBEGIN<\/span> \n    SELECT<\/span> product_name, price \n    INTO<\/span> v_product\n    FROM<\/span> products\n    WHERE<\/span> product_id = id;\n    \n    IF<\/span> FOUND<\/span> THEN<\/span>\n        RETURN<\/span> v_product.price;\n    ELSE<\/span> \n        RETURN<\/span> NULL<\/span>;\n    END<\/span> IF<\/span>;\nEND<\/span>;\n$$<\/span>\nLANGUAGE<\/span> plpgsql;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works<\/p>\n\n\n\n
First, declare a record variable v_product<\/code> with the type RECORD<\/code>:<\/p>\n\n\n
DECLARE<\/span> v_product RECORD<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, retrieve the row from the products<\/code> table with the product_id<\/code> 1 and assign it to the v_product<\/code> record variable:<\/p>\n\n\n
SELECT<\/span> product_name, price \nINTO<\/span> v_product \nFROM<\/span> products \nWHERE<\/span> product_id = 1<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the the product price if the product exists or NULL otherwise:<\/p>\n\n\n
IF<\/span> FOUND<\/span> THEN<\/span>\n   RETURN<\/span> v_product.price;\nELSE<\/span> \n   RETURN<\/span> NULL<\/span>;\nEND<\/span> IF<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can call the get_price function to retrieve the price of the product with id 1:<\/p>\n\n\n
SELECT<\/span> get_price(1<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 get_price\n-----------\n    999.99<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Using a RECORD variable with a FOR loop #<\/a><\/h2>\n\n\n\n
The following shows how to select the top three most expensive products and print them on the screen:<\/p>\n\n\n
DO<\/span> $$\nDECLARE<\/span>\n    v_product RECORD<\/span>;\nBEGIN<\/span> \n    \nFOR<\/span> v_product IN<\/span> SELECT<\/span> product_name, price \n    FROM<\/span> products\n    ORDER<\/span> BY<\/span> price DESC<\/span>, product_name  LIMIT<\/span> 3<\/span>\nLOOP<\/span>    \n        RAISE<\/span> NOTICE<\/span> '%: $%'<\/span>,  v_product.product_name, v_product.price;\nEND<\/span> LOOP<\/span>;\nEND<\/span>;\n$$<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
First, declare a variable with the type RECORD<\/code> in the declaration section: <\/p>\n\n\n
DECLARE<\/span> v_product RECORD<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use a FOR<\/code> statement to retrieve the top 3 most expensive products and assign each to the record variable:<\/p>\n\n\n
FOR<\/span> v_product IN<\/span> SELECT<\/span> product_name, price \n    FROM<\/span> products\n    ORDER<\/span> BY<\/span> price DESC<\/span>, product_name  LIMIT<\/span> 3<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display the product name and price in each iteration of the loop:<\/p>\n\n\n
RAISE<\/span> NOTICE<\/span> '%: $%'<\/span>, v_product.product_name, v_product.price;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Dynamic query execution with RECORD #<\/a><\/h2>\n\n\n\n
To execute a dynamic query, you use the EXECUTE<\/code> statement:<\/p>\n\n\n
EXECUTE<\/span>  query;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the query<\/code> is a literal string representing a query to execute.<\/p>\n\n\n\n
When you execute a dynamic query, you only know the structure of the result set at runtime. In this case, a RECORD<\/code> variable is ideal for handling the dynamic result.<\/p>\n\n\n\n
Here’s the syntax for executing a dynamic query and assigning the row to a record:<\/p>\n\n\n
EXECUTE<\/span> query INTO<\/span> record_variable;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example defines a function get_record<\/code> that executes a dynamic query and returns a record.<\/p>\n\n\n
CREATE<\/span> FUNCTION<\/span> get_record (query TEXT<\/span>) \nRETURNS<\/span> RECORD<\/span> AS<\/span> $$\nDECLARE<\/span>\n    rec RECORD<\/span>;\nBEGIN<\/span>\n    EXECUTE<\/span> query INTO<\/span> rec;\n    RETURN<\/span> rec; \nEND<\/span>;\n$$<\/span> LANGUAGE<\/span> plpgsql;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To call the get_record<\/code> function, you need to cast the record explicitly into a specific type:<\/p>\n\n\n
SELECT<\/span>\n  *\nFROM<\/span>\n  get_record (\n    'SELECT product_name, price FROM products WHERE product_id = 1'<\/span>\n  ) AS<\/span> product_info (product_name VARCHAR<\/span>, price DEC<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
    product_name    | price\n--------------------+--------\n Samsung Galaxy S24 | 999.99<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
The RECORD<\/code> 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<\/a><\/code> or FOR<\/a><\/code> statement.<\/p>\n\n\n\n