{"id":1621,"date":"2025-01-11T23:26:09","date_gmt":"2025-01-11T16:26:09","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1621"},"modified":"2025-01-12T08:51:48","modified_gmt":"2025-01-12T01:51:48","slug":"plpgsql-row-types","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-row-types\/","title":{"rendered":"PL\/pgSQL Row Types"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.<\/p>\n\n\n\n

Overview of PL\/pgSQL Row Types #<\/a><\/h2>\n\n\n\n

A composite type<\/a> is a type that contains a list of fields and their corresponding types. In PL\/pgSQL, a variable of a composite type is called a row-type variable (or row variable).<\/p>\n\n\n\n

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

variable_name composite_type;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PL\/pgSQL allows you to declare a row variable with the same type as the row of a table with the following syntax:<\/p>\n\n\n
variable_name table_name<\/span>%ROWTYPE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
variable_name view_name%ROWTYPE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you create a table, PostgreSQL implicitly creates a corresponding composite type with the same name. For example, PostgreSQL automatically creates the profiles<\/code> composite type when you create the profiles<\/code> table:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Therefore, the following row-type variable declarations are the same:<\/p>\n\n\n
v_profile profiles;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
and<\/p>\n\n\n
v_profile profiles%ROWTYPE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Basic Example of PL\/pgSQL Row Types #<\/a><\/h2>\n\n\n\n
The following defines a function called get_contact<\/code> that accepts an id and returns the contact of a user from the profiles<\/code> table:<\/p>\n\n\n
CREATE<\/span> FUNCTION<\/span> get_contact (id INT<\/span>) RETURNS<\/span> TEXT<\/span> AS<\/span> \n$$\nDECLARE<\/span> \n    v_profile profiles%ROWTYPE<\/span>;\nBEGIN<\/span>\n   SELECT<\/span> * INTO<\/span> v_profile\n   FROM<\/span> profiles\n   WHERE<\/span> user_id = id;\n\u00a0 \u00a0\n   RETURN<\/span> v_profile.first_name || '  '<\/span> || v_profile.last_name || ' <'<\/span> || v_profile.work_phone || '>'<\/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 the variable v_profile<\/code> with the row type of the profiles<\/code> table:<\/p>\n\n\n
DECLARE<\/span> \n\u00a0 \u00a0v_profile profiles%ROWTYPE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, select a row from the profiles<\/code> table and assign it to the row type variable v_profile<\/code>:<\/p>\n\n\n
SELECT<\/span> * INTO<\/span> v_profile\nFROM<\/span> profiles\nWHERE<\/span> user_id = id;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, concatenate the first name, last name, and work phone of the user into a single string and return it:<\/p>\n\n\n
RETURN<\/span> v_profile.first_name || '  '<\/span> || v_profile.last_name || ' <'<\/span> || v_profile.work_phone || '>'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement calls the get_contact() function to get the contact of the user id 1:<\/p>\n\n\n
SELECT<\/span> get_contact(1<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Returning a value with the row type #<\/a><\/h2>\n\n\n\n
The following statement create a function<\/a> that returns a profile based on a user id:<\/p>\n\n\n
CREATE<\/span> FUNCTION<\/span> find_profile_by_id(id INT<\/span>)\nRETURNS<\/span> profiles\nAS<\/span>\n$$\nDECLARE<\/span>\n   v_profile profiles%ROWTYPE<\/span>;\nBEGIN<\/span>\n   SELECT<\/span> * INTO<\/span> v_profile\n   FROM<\/span> profiles\n   WHERE<\/span> user_id = id;\n   \n   RETURN<\/span> v_profile;\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 the function works:<\/p>\n\n\n\n
First, declare the variable v_profile<\/code>with the row type of the profiles<\/code> table:<\/p>\n\n\n
DECLARE<\/span> \n\u00a0 \u00a0v_profile profiles%ROWTYPE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, select a row from the profiles<\/code> table and assign it to the row type variable v_profile<\/code>:<\/p>\n\n\n
SELECT<\/span> * INTO<\/span> v_profile \nFROM<\/span> profiles\nWHERE<\/span> user_id = id;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, return the row type variable:<\/p>\n\n\n
RETURN<\/span> v_profile;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement calls the find_profile_by_id<\/code> function to find the profile of a user by id:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> find_profile_by_id(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
 user_id | first_name | last_name |  work_phone  |  home_phone\n---------+------------+-----------+--------------+--------------<\/span>\n       1<\/span> | John       | Doe       | 408<\/span>-456<\/span>-7890<\/span> | 408<\/span>-111<\/span>-2222<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the PL\/pgSQL row-type variables to store an entire table row in a variable.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
PostgreSQL recommends using the %ROWTYPE<\/code> syntax to make the code more portable, such as PL\/SQL record type<\/a> in Oracle Database.<\/p>\n\n\n\n
Besides a table name, you can use a view name<\/a> as follows:<\/p>\n\n\n
The row variable can hold an entire row of a SELECT statement<\/a> or FOR query result<\/a> if the query’s columns match the fields of the composite type.<\/p>\n\n\n\n