{"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<p class=\"wp-block-paragraph\"><strong>Summary<\/strong>: In this tutorial, you&#8217;ll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='overview-of-plpgsql-row-types'>Overview of PL\/pgSQL Row Types <a href=\"#overview-of-plpgsql-row-types\" class=\"anchor\" id=\"overview-of-plpgsql-row-types\" title=\"Anchor for Overview of PL\/pgSQL Row Types\">#<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-composite-types\/\">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<p class=\"wp-block-paragraph\">Here&#8217;s the syntax for declaring a row variable:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"dmFyaWFibGVfbmFtZSBjb21wb3NpdGVfdHlwZTs=\"><code class=\"ssh-highlighted hljs language-pgsql\">variable_name composite_type;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The row variable can hold an entire row of a <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-select\/\">SELECT statement<\/a> or <a href=\"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-for-loop\/\">FOR query result<\/a> if the query&#8217;s columns match the fields of the composite type.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"dmFyaWFibGVfbmFtZSB0YWJsZV9uYW1lJVJPV1RZUEU7\"><code class=\"ssh-highlighted hljs language-pgsql\">variable_name <span class=\"hljs-built_in\">table_name<\/span><span class=\"hljs-meta\">%ROWTYPE<\/span>;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Besides a table name, you can use a <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-view\/\">view name<\/a> as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"dmFyaWFibGVfbmFtZSB2aWV3X25hbWUlUk9XVFlQRTs=\"><code class=\"ssh-highlighted hljs language-pgsql\">variable_name view_name<span class=\"hljs-meta\">%ROWTYPE<\/span>;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">When you create a table, PostgreSQL implicitly creates a corresponding composite type with the same name. For example, PostgreSQL automatically creates the <code>profiles<\/code> composite type when you create the <code>profiles<\/code> table:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"144\" height=\"162\" src=\"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/profiles.png\" alt=\"\" class=\"wp-image-1052\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Therefore, the following row-type variable declarations are the same:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"dl9wcm9maWxlIHByb2ZpbGVzOw==\"><code class=\"ssh-highlighted hljs language-pgsql\">v_profile profiles;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"dl9wcm9maWxlIHByb2ZpbGVzJVJPV1RZUEU7\"><code class=\"ssh-highlighted hljs language-pgsql\">v_profile profiles<span class=\"hljs-meta\">%ROWTYPE<\/span>;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL recommends using the <code>%ROWTYPE<\/code> syntax to make the code more portable, such as <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-record\/\">PL\/SQL record type<\/a> in Oracle Database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='basic-example-of-plpgsql-row-types'>Basic Example of PL\/pgSQL Row Types <a href=\"#basic-example-of-plpgsql-row-types\" class=\"anchor\" id=\"basic-example-of-plpgsql-row-types\" title=\"Anchor for Basic Example of PL\/pgSQL Row Types\">#<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The following defines a function called <code>get_contact<\/code> that accepts an id and returns the contact of a user from the <code>profiles<\/code> table:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"Q1JFQVRFIEZVTkNUSU9OIGdldF9jb250YWN0IChpZCBJTlQpIFJFVFVSTlMgVEVYVCBBUyAKJCQKREVDTEFSRSAKICAgIHZfcHJvZmlsZSBwcm9maWxlcyVST1dUWVBFOwpCRUdJTgogICBTRUxFQ1QgKiBJTlRPIHZfcHJvZmlsZQogICBGUk9NIHByb2ZpbGVzCiAgIFdIRVJFIHVzZXJfaWQgPSBpZDsKwqAgwqAKICAgUkVUVVJOIHZfcHJvZmlsZS5maXJzdF9uYW1lIHx8ICcgICcgfHwgdl9wcm9maWxlLmxhc3RfbmFtZSB8fCAnIDwnIHx8IHZfcHJvZmlsZS53b3JrX3Bob25lIHx8ICc+JzsKRU5EOwokJApMQU5HVUFHRSBwbHBnc3FsOw==\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> get_contact (id <span class=\"hljs-type\">INT<\/span>) <span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-type\">TEXT<\/span> <span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">DECLARE<\/span> \n    v_profile profiles<span class=\"hljs-meta\">%ROWTYPE<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n   <span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">INTO<\/span> v_profile\n   <span class=\"hljs-keyword\">FROM<\/span> profiles\n   <span class=\"hljs-keyword\">WHERE<\/span> user_id = id;\n\u00a0 \u00a0\n   <span class=\"hljs-keyword\">RETURN<\/span> v_profile.first_name || <span class=\"hljs-string\">'  '<\/span> || v_profile.last_name || <span class=\"hljs-string\">' &lt;'<\/span> || v_profile.work_phone || <span class=\"hljs-string\">'&gt;'<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;\n$$<\/span>\n<span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIEZVTkNUSU9OIGdldF9jb250YWN0IChpZCBJTlQpIFJFVFVSTlMgVEVYVCBBUyAKJCQKREVDTEFSRSAKICAgIHZfcHJvZmlsZSBwcm9maWxlcyVST1dUWVBFOwpCRUdJTgogICBTRUxFQ1QgKiBJTlRPIHZfcHJvZmlsZQogICBGUk9NIHByb2ZpbGVzCiAgIFdIRVJFIHVzZXJfaWQgPSBpZDsKoCCgCiAgIFJFVFVSTiB2X3Byb2ZpbGUuZmlyc3RfbmFtZSB8fCAnICAnIHx8IHZfcHJvZmlsZS5sYXN0X25hbWUgfHwgJyA8JyB8fCB2X3Byb2ZpbGUud29ya19waG9uZSB8fCAnPic7CkVORDsKJCQKTEFOR1VBR0UgcGxwZ3NxbDs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">How it works:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First, declare the variable <code>v_profile<\/code> with the row type of the <code>profiles<\/code> table:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"REVDTEFSRSAKwqAgwqB2X3Byb2ZpbGUgcHJvZmlsZXMlUk9XVFlQRTs=\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">DECLARE<\/span> \n\u00a0 \u00a0v_profile profiles<span class=\"hljs-meta\">%ROWTYPE<\/span>;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Second, select a row from the <code>profiles<\/code> table and assign it to the row type variable <code>v_profile<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"U0VMRUNUICogSU5UTyB2X3Byb2ZpbGUKRlJPTSBwcm9maWxlcwpXSEVSRSB1c2VyX2lkID0gaWQ7\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">INTO<\/span> v_profile\n<span class=\"hljs-keyword\">FROM<\/span> profiles\n<span class=\"hljs-keyword\">WHERE<\/span> user_id = id;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Third, concatenate the first name, last name, and work phone of the user into a single string and return it:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"UkVUVVJOIHZfcHJvZmlsZS5maXJzdF9uYW1lIHx8ICcgICcgfHwgdl9wcm9maWxlLmxhc3RfbmFtZSB8fCAnIDwnIHx8IHZfcHJvZmlsZS53b3JrX3Bob25lIHx8ICc+Jzs=\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">RETURN<\/span> v_profile.first_name || <span class=\"hljs-string\">'  '<\/span> || v_profile.last_name || <span class=\"hljs-string\">' &lt;'<\/span> || v_profile.work_phone || <span class=\"hljs-string\">'&gt;'<\/span>;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The following statement calls the get_contact() function to get the contact of the user id 1:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"U0VMRUNUIGdldF9jb250YWN0KDEpOw==\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> get_contact(<span class=\"hljs-number\">1<\/span>);<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id='returning-a-value-with-the-row-type'>Returning a value with the row type <a href=\"#returning-a-value-with-the-row-type\" class=\"anchor\" id=\"returning-a-value-with-the-row-type\" title=\"Anchor for Returning a value with the row type\">#<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The following statement <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\">create a function<\/a> that returns a profile based on a user id:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"Q1JFQVRFIEZVTkNUSU9OIGZpbmRfcHJvZmlsZV9ieV9pZChpZCBJTlQpClJFVFVSTlMgcHJvZmlsZXMKQVMKJCQKREVDTEFSRQogICB2X3Byb2ZpbGUgcHJvZmlsZXMlUk9XVFlQRTsKQkVHSU4KICAgU0VMRUNUICogSU5UTyB2X3Byb2ZpbGUKICAgRlJPTSBwcm9maWxlcwogICBXSEVSRSB1c2VyX2lkID0gaWQ7CiAgIAogICBSRVRVUk4gdl9wcm9maWxlOwpFTkQ7CiQkCkxBTkdVQUdFIHBscGdzcWw7\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> find_profile_by_id(id <span class=\"hljs-type\">INT<\/span>)\n<span class=\"hljs-keyword\">RETURNS<\/span> profiles\n<span class=\"hljs-keyword\">AS<\/span>\n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">DECLARE<\/span>\n   v_profile profiles<span class=\"hljs-meta\">%ROWTYPE<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n   <span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">INTO<\/span> v_profile\n   <span class=\"hljs-keyword\">FROM<\/span> profiles\n   <span class=\"hljs-keyword\">WHERE<\/span> user_id = id;\n   \n   <span class=\"hljs-keyword\">RETURN<\/span> v_profile;\n<span class=\"hljs-keyword\">END<\/span>;\n$$<\/span>\n<span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIEZVTkNUSU9OIGZpbmRfcHJvZmlsZV9ieV9pZChpZCBJTlQpClJFVFVSTlMgcHJvZmlsZXMKQVMKJCQKREVDTEFSRQogICB2X3Byb2ZpbGUgcHJvZmlsZXMlUk9XVFlQRTsKQkVHSU4KICAgU0VMRUNUICogSU5UTyB2X3Byb2ZpbGUKICAgRlJPTSBwcm9maWxlcwogICBXSEVSRSB1c2VyX2lkID0gaWQ7CiAgIAogICBSRVRVUk4gdl9wcm9maWxlOwpFTkQ7CiQkCkxBTkdVQUdFIHBscGdzcWw7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">How the function works:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First, declare the variable <code>v_profile<\/code>with the row type of the <code>profiles<\/code> table:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"REVDTEFSRSAKwqAgwqB2X3Byb2ZpbGUgcHJvZmlsZXMlUk9XVFlQRTs=\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">DECLARE<\/span> \n\u00a0 \u00a0v_profile profiles<span class=\"hljs-meta\">%ROWTYPE<\/span>;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Second, select a row from the <code>profiles<\/code> table and assign it to the row type variable <code>v_profile<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"U0VMRUNUICogSU5UTyB2X3Byb2ZpbGUgCkZST00gcHJvZmlsZXMKV0hFUkUgdXNlcl9pZCA9IGlkOw==\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">INTO<\/span> v_profile \n<span class=\"hljs-keyword\">FROM<\/span> profiles\n<span class=\"hljs-keyword\">WHERE<\/span> user_id = id;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Third, return the row type variable:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"UkVUVVJOIHZfcHJvZmlsZTs=\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">RETURN<\/span> v_profile;<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The following statement calls the <code>find_profile_by_id<\/code> function to find the profile of a user by id:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"U0VMRUNUICogRlJPTSBmaW5kX3Byb2ZpbGVfYnlfaWQoMSk7\"><code class=\"ssh-highlighted hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> find_profile_by_id(<span class=\"hljs-number\">1<\/span>);<\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUICogRlJPTSBmaW5kX3Byb2ZpbGVfYnlfaWQoMSk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Output:<\/p>\n\n\n\n<pre class=\"wp-block-code ssh-code-block\" data-ssh-language=\"pgsql\" data-ssh-source=\"IHVzZXJfaWQgfCBmaXJzdF9uYW1lIHwgbGFzdF9uYW1lIHwgIHdvcmtfcGhvbmUgIHwgIGhvbWVfcGhvbmUKLS0tLS0tLS0tKy0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLQogICAgICAgMSB8IEpvaG4gICAgICAgfCBEb2UgICAgICAgfCA0MDgtNDU2LTc4OTAgfCA0MDgtMTExLTIyMjI=\"><code class=\"ssh-highlighted hljs language-pgsql\"> user_id | first_name | last_name |  work_phone  |  home_phone\n<span class=\"hljs-comment\">---------+------------+-----------+--------------+--------------<\/span>\n       <span class=\"hljs-number\">1<\/span> | John       | Doe       | <span class=\"hljs-number\">408<\/span><span class=\"hljs-number\">-456<\/span><span class=\"hljs-number\">-7890<\/span> | <span class=\"hljs-number\">408<\/span><span class=\"hljs-number\">-111<\/span><span class=\"hljs-number\">-2222<\/span><\/code><button type=\"button\" class=\"ssh-copy-button\" aria-label=\"Copy code\" title=\"Copy code\"><span class=\"ssh-copy-icon\" aria-hidden=\"true\"><\/span><span class=\"ssh-copy-label\">Copy<\/span><\/button><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the PL\/pgSQL row-type variables to store an entire table row in a variable.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Summary: In this tutorial, you&#8217;ll learn how to use PL\/pgSQL row types to declare variables that can hold a table row. Overview of PL\/pgSQL Row Types # A composite type 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1420,"menu_order":2,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1621","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PL\/pgSQL Row Types<\/title>\n<meta name=\"description\" content=\"In this tutorial, you&#039;ll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PL\/pgSQL Row Types\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you&#039;ll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-12T01:51:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/profiles.png\" \/>\n\t<meta property=\"og:image:width\" content=\"144\" \/>\n\t<meta property=\"og:image:height\" content=\"162\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/\",\"name\":\"PL\\\/pgSQL Row Types\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.pgtutorial.com\\\/wp-content\\\/uploads\\\/2024\\\/12\\\/profiles.png\",\"datePublished\":\"2025-01-11T16:26:09+00:00\",\"dateModified\":\"2025-01-12T01:51:48+00:00\",\"description\":\"In this tutorial, you'll learn how to use PL\\\/pgSQL row types to declare variables that can hold a table row.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/wp-content\\\/uploads\\\/2024\\\/12\\\/profiles.png\",\"contentUrl\":\"https:\\\/\\\/www.pgtutorial.com\\\/wp-content\\\/uploads\\\/2024\\\/12\\\/profiles.png\",\"width\":144,\"height\":162},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/plpgsql-row-types\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Tutorial\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PL\\\/pgSQL Row Types\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PL\/pgSQL Row Types","description":"In this tutorial, you'll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/","og_locale":"en_US","og_type":"article","og_title":"PL\/pgSQL Row Types","og_description":"In this tutorial, you'll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.","og_url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2025-01-12T01:51:48+00:00","og_image":[{"width":144,"height":162,"url":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/profiles.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/","url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/","name":"PL\/pgSQL Row Types","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/#primaryimage"},"image":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/#primaryimage"},"thumbnailUrl":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/profiles.png","datePublished":"2025-01-11T16:26:09+00:00","dateModified":"2025-01-12T01:51:48+00:00","description":"In this tutorial, you'll learn how to use PL\/pgSQL row types to declare variables that can hold a table row.","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/#primaryimage","url":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/profiles.png","contentUrl":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/profiles.png","width":144,"height":162},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/plpgsql-row-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Tutorial","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":3,"name":"PL\/pgSQL Row Types"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1621","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=1621"}],"version-history":[{"count":8,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1621\/revisions"}],"predecessor-version":[{"id":1630,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1621\/revisions\/1630"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1420"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=1621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}