Skip to content

PostgreSQL - Row Type Variables

PostgreSQL - Row Type Variables

Last Updated : 23 Aug, 2024

In PostgreSQL, row type variables are handy when you need to store a whole row of data returned by a query. They are particularly useful when dealing with SELECT INTO statements where the result contains multiple columns from a table or view. This feature simplifies handling data from complex queries and makes your PL/pgSQL code more readable and maintainable.

In this article, we'll learn to declare and use row type variables in PostgreSQL, access specific fields within them using dot notation, and provide practical examples to help you understand their application.

What are Row Type Variables in PostgreSQL?

Row type variables in PostgreSQL allow you to store an entire row from a table or view in a single variable. The row is stored as a composite data type, which includes multiple columns that you can access individually.

These variables are declared using the '%ROWTYPE' attribute, which automatically matches the structure of the row to the table or view you reference. This feature is beneficial when working with complex queries that return multiple columns, allowing you to handle the data more flexibly.

How to Declare Row Type Variables

We can declare a row type variable that has the same data type as the corresponding datatype in the row of the table or the view using the following syntax.

Syntax: row_variable table_name/view_name %ROWTYPE;

We can use the dot notation (.) to access any field from the row variable.

Syntax: row_variable.field_name

PostgreSQL Row Type Variables Examples

First, we create a sample table using the below commands to perform examples:

PostgreSQL

CREATE TABLE employees (
 employee_id serial PRIMARY KEY,
 full_name VARCHAR NOT NULL,
 manager_id INT
);
INSERT INTO employees (
 employee_id,
 full_name,
 manager_id
)
VALUES
 (1, 'M.S Dhoni', NULL),
 (2, 'Sachin Tendulkar', 1),
 (3, 'R. Sharma', 1),
 (4, 'S. Raina', 1),
 (5, 'B. Kumar', 1),
 (6, 'Y. Singh', 2),
 (7, 'Virender Sehwag ', 2),
 (8, 'Ajinkya Rahane', 2),
 (9, 'Shikhar Dhawan', 2),
 (10, 'Mohammed Shami', 3),
 (11, 'Shreyas Iyer', 3),
 (12, 'Mayank Agarwal', 3),
 (13, 'K. L. Rahul', 3),
 (14, 'Hardik Pandya', 4),
 (15, 'Dinesh Karthik', 4),
 (16, 'Jasprit Bumrah', 7),
 (17, 'Kuldeep Yadav', 7),
 (18, 'Yuzvendra Chahal', 8),
 (19, 'Rishabh Pant', 8),
 (20, 'Sanju Samson', 8);

The table is:

Example 1: Selecting a Single Row with All Columns

The following will help create a row type variable sel_employee from the table employees

DO $$
DECLARE
  sel_employee employees%ROWTYPE;
BEGIN
  -- Select employee with ID 6
  SELECT * INTO sel_employee
  FROM employees
  WHERE employee_id = 6;
  
  RAISE NOTICE 'The employee name is % and the manager ID is %',
    sel_employee.full_name,
    sel_employee.manager_id;
END $$;

Output:

Explanation:

Row Type Variable: The 'sel_employee' variable can store an entire row from the employees table.
SELECT INTO Statement: The query selects all columns of the row where 'employee_id = 6' and stores it into sel_employee.
RAISE NOTICE: This statement outputs the employee’s name and manager ID, demonstrating how to access individual fields.

Example 2: Selecting a Single Row with Specific Columns

The following will help create a row type variable 'sel_employee' from the table employees with 2 columns 'employee_id' and 'full_name'.

DO $$
DECLARE
  sel_employee employees%ROWTYPE;
BEGIN
  -- Select employee with ID 12
  SELECT employee_id, full_name  
  INTO sel_employee
  FROM employees
  WHERE employee_id = 12;
  
  RAISE NOTICE 'The employee name is % and the length of the name is %',
    sel_employee.full_name,
    LENGTH(sel_employee.full_name);
END $$;

Output:

Explanation:

Selecting Specific Columns: The query selects only 'employee_id' and 'full_name', making the row type variable more efficient for specific use cases.
RAISE NOTICE: The output message shows the selected employee’s name and the length of the name using the 'LENGTH()' function.

Important Points About PostgreSQL Row Type Variables

Always use the %ROWTYPE attribute when dealing with tables whose structure may change over time, as it automatically adapts to any schema changes.
Use dot notation for easy access to individual fields, improving code readability and maintainability.
Row type variables are excellent for batch processing when you need to fetch and process multiple rows iteratively.

PostgreSQL - Row Type Variables

A

ashutoshrathi

Improve

Article Tags :

Similar Reads

PostgreSQL - Record Type Variable

In PostgreSQL, record-type variables provide a dynamic and flexible way to handle result sets that don't have a predefined structure. Unlike row-type variables, PostgreSQL record-type variables can change their structure after being assigned a new row, making them highly versatile for dynamic SQL op

PostgreSQL - Variables

PostgreSQL, one of the most powerful and advanced open-source relational database management systems, provides robust support for procedural programming through its PL/pgSQL language. A fundamental aspect of PL/pgSQL is the use of variables that play a crucial role in storing temporary data and faci

PostgreSQL - VARCHAR Data Type

In the world of relational databases, PostgreSQL stands out with its robust support for various data types, including the flexible VARCHAR data type. This character data type allows us to store strings of variable length, making it an essential choice for many applications.In this article, we will e

PostgreSQL - SERIAL

When working with PostgreSQL, we need to create tables with unique primary keys. PostgreSQL offers a powerful feature known as the SERIAL pseudo-type which simplifies generating auto-incrementing sequences for columns. In this article, weâ€™ll learn about the PostgreSQL SERIAL pseudo-type by explain h

PostgreSQL Tutorial

In this PostgreSQL tutorial youâ€™ll learn the basic data types(Boolean, char, text, time, int etc.), Querying and Filtering techniques like select, where, in, order by, etc. managing and modifying the tables in PostgreSQL. Weâ€™ll cover all the basic to advance concepts of PostgreSQL in this tutorial.

PostgreSQL - Data Types

PostgreSQL is a powerful, open-source relational database management system that supports a wide variety of data types. These data types are essential for defining the nature of the data stored in a database column. which allows developers to define, store, and manipulate data in a way that aligns w

PostgreSQL - Size of value

The size of a value in a database table refers to the space required to store that specific value. Accurately gauging the storage requirements for different data types can be crucial for database optimization and performance. In this article, we will explore the 'pg_column_size()' function in Postgr

PostgreSQL - CREATE TABLE

In PostgreSQL, the CREATE TABLE statement is used to define a new table within a database. It allows us to specify the table's structure, including column names, data types, and constraints, ensuring data integrity and consistency. Understanding the PostgreSQL table creation process is essential for

PostgreSQL - SELECT

PostgreSQL SELECT statement is an command for retrieving data from tables within a PostgreSQL database. It enables users to specify which columns to fetch and apply filters using the WHERE clause for targeted results.In this article, We will learn about the PostgreSQL SELECT in detail by understandi

PostgreSQL - TEXT Data Type

PostgreSQL provides a highly flexible character data type known as TEXT, designed to store character strings of virtually unlimited length. Unlike the VARCHAR data type, which can be limited to a specified length, the TEXT data type offers the same efficiency and performance without the length const

'); // $('.spinner-loading-overlay').show(); let script = document.createElement('script'); script.src = 'https://assets.geeksforgeeks.org/v2/editor-prod/static/js/bundle.min.js'; script.defer = true document.head.appendChild(script); script.onload = function() { suggestionModalEditor() //to add editor in suggestion modal if(loginData && loginData.premiumConsent){ personalNoteEditor() //to load editor in personal note } } script.onerror = function() { if($('.editorError').length){ $('.editorError').remove(); } var messageDiv = $('

').text('Editor not loaded due to some issues'); $('#suggestion-section-textarea').append(messageDiv); $('.suggest-bottom-btn').hide(); $('.suggestion-section').hide(); editorLoaded = false; } }); //suggestion modal editor function suggestionModalEditor(){ // editor params const params = { data: undefined, plugins: ["BOLD", "ITALIC", "UNDERLINE", "PREBLOCK"], } // loading editor try { suggestEditorInstance = new GFGEditorWrapper("suggestion-section-textarea", params, { appNode: true }) suggestEditorInstance._createEditor("") $('.spinner-loading-overlay:eq(0)').remove(); editorLoaded = true; } catch (error) { $('.spinner-loading-overlay:eq(0)').remove(); editorLoaded = false; } } //personal note editor function personalNoteEditor(){ // editor params const params = { data: undefined, plugins: ["UNDO", "REDO", "BOLD", "ITALIC", "NUMBERED_LIST", "BULLET_LIST", "TEXTALIGNMENTDROPDOWN"], placeholderText: "Description to be......", } // loading editor try { let notesEditorInstance = new GFGEditorWrapper("pn-editor", params, { appNode: true }) notesEditorInstance._createEditor(loginData&&loginData.user_personal_note?loginData.user_personal_note:"") $('.spinner-loading-overlay:eq(0)').remove(); editorLoaded = true; } catch (error) { $('.spinner-loading-overlay:eq(0)').remove(); editorLoaded = false; } } var lockedCasesHtml = `You can suggest the changes for now and it will be under 'My Suggestions' Tab on Write.

You will be notified via email once the article is available for improvement. Thank you for your valuable feedback!`; var badgesRequiredHtml = `It seems that you do not meet the eligibility criteria to create improvements for this article, as only users who have earned specific badges are permitted to do so.

However, you can still create improvements through the Pick for Improvement section.`; jQuery('.improve-header-sec-child').on('click', function(){ jQuery('.improve-modal--overlay').hide(); $('.improve-modal--suggestion').hide(); jQuery('#suggestion-modal-alert').hide(); }); $('.suggest-change_wrapper, .locked-status--impove-modal .improve-bottom-btn').on('click',function(){ // when suggest changes option is clicked $('.ContentEditable__root').text(""); $('.suggest-bottom-btn').html("Suggest changes"); $('.thank-you-message').css("display","none"); $('.improve-modal--improvement').hide(); $('.improve-modal--suggestion').show(); $('#suggestion-section-textarea').show(); jQuery('#suggestion-modal-alert').hide(); if(suggestEditorInstance !== null){ suggestEditorInstance.setEditorValue(""); } $('.suggestion-section').css('display', 'block'); jQuery('.suggest-bottom-btn').css("display","block"); }); $('.create-improvement_wrapper').on('click',function(){ // when create improvement option clicked then improvement reason will be shown if(loginData && loginData.isLoggedIn) { $('body').append('

'); $('.spinner-loading-overlay').show(); jQuery.ajax({ url: writeApiUrl + 'create-improvement-post/?v=1', type: "POST", contentType: 'application/json; charset=utf-8', dataType: 'json', xhrFields: { withCredentials: true }, data: JSON.stringify({ gfg_id: post_id }), success:function(result) { $('.spinner-loading-overlay:eq(0)').remove(); $('.improve-modal--overlay').hide(); $('.unlocked-status--improve-modal-content').css("display","none"); $('.create-improvement-redirection-to-write').attr('href',writeUrl + 'improve-post/' + `${result.id}` + '/', '_blank'); $('.create-improvement-redirection-to-write')[0].click(); }, error:function(e) { showErrorMessage(e.responseJSON,e.status) }, }); } else { if(loginData && !loginData.isLoggedIn) { $('.improve-modal--overlay').hide(); if ($('.header-main__wrapper').find('.header-main__signup.login-modal-btn').length) { $('.header-main__wrapper').find('.header-main__signup.login-modal-btn').click(); } return; } } }); $('.left-arrow-icon_wrapper').on('click',function(){ if($('.improve-modal--suggestion').is(":visible")) $('.improve-modal--suggestion').hide(); else{ } $('.improve-modal--improvement').show(); }); const showErrorMessage = (result,statusCode) => { if(!result) return; $('.spinner-loading-overlay:eq(0)').remove(); if(statusCode == 403) { $('.improve-modal--improve-content.error-message').html(result.message); jQuery('.improve-modal--overlay').show(); jQuery('.improve-modal--improvement').show(); $('.locked-status--impove-modal').css("display","block"); $('.unlocked-status--improve-modal-content').css("display","none"); $('.improve-modal--improvement').attr("status","locked"); return; } } function suggestionCall() { var editorValue = suggestEditorInstance.getValue(); var suggest_val = $(".ContentEditable__root").find("[data-lexical-text='true']").map(function() { return $(this).text().trim(); }).get().join(' '); suggest_val = suggest_val.replace(/\s+/g, ' ').trim(); var array_String= suggest_val.split(" ") //array of words var gCaptchaToken = $("#g-recaptcha-response-suggestion-form").val(); var error_msg = false; if(suggest_val != "" && array_String.length >=4){ if(editorValue.length <= 2000){ var payload = { "gfg_post_id" : `${post_id}`, "suggestion" : `${editorValue}`, } if(!loginData || !loginData.isLoggedIn) // User is not logged in payload["g-recaptcha-token"] = gCaptchaToken jQuery.ajax({ type:'post', url: "https://apiwrite.geeksforgeeks.org/suggestions/auth/create/", xhrFields: { withCredentials: true }, crossDomain: true, contentType:'application/json', data: JSON.stringify(payload), success:function(data) { if(!loginData || !loginData.isLoggedIn) { grecaptcha.reset(); } jQuery('.spinner-loading-overlay:eq(0)').remove(); jQuery('.suggest-bottom-btn').css("display","none"); $('#suggestion-section-textarea').hide() $('.thank-you-message').css('display', 'flex'); $('.suggestion-section').css('display', 'none'); jQuery('#suggestion-modal-alert').hide(); }, error:function(data) { if(!loginData || !loginData.isLoggedIn) { grecaptcha.reset(); } jQuery('.spinner-loading-overlay:eq(0)').remove(); jQuery('#suggestion-modal-alert').html("Something went wrong."); jQuery('#suggestion-modal-alert').show(); error_msg = true; } }); } else{ jQuery('.spinner-loading-overlay:eq(0)').remove(); jQuery('#suggestion-modal-alert').html("Minimum 4 Words and Maximum Words limit is 1000."); jQuery('#suggestion-modal-alert').show(); jQuery('.ContentEditable__root').focus(); error_msg = true; } } else{ jQuery('.spinner-loading-overlay:eq(0)').remove(); jQuery('#suggestion-modal-alert').html("Enter atleast four words !"); jQuery('#suggestion-modal-alert').show(); jQuery('.ContentEditable__root').focus(); error_msg = true; } if(error_msg){ setTimeout(() => { jQuery('.ContentEditable__root').focus(); jQuery('#suggestion-modal-alert').hide(); }, 3000); } } document.querySelector('.suggest-bottom-btn').addEventListener('click', function(){ jQuery('body').append('

'); jQuery('.spinner-loading-overlay').show(); if(loginData && loginData.isLoggedIn) { suggestionCall(); return; } // script for grecaptcha loaded in loginmodal.html and call function to set the token setGoogleRecaptcha(); }); $('.improvement-bottom-btn.create-improvement-btn').click(function() { //create improvement button is clicked $('body').append('

'); $('.spinner-loading-overlay').show(); // send this option via create-improvement-post api jQuery.ajax({ url: writeApiUrl + 'create-improvement-post/?v=1', type: "POST", contentType: 'application/json; charset=utf-8', dataType: 'json', xhrFields: { withCredentials: true }, data: JSON.stringify({ gfg_id: post_id }), success:function(result) { $('.spinner-loading-overlay:eq(0)').remove(); $('.improve-modal--overlay').hide(); $('.create-improvement-redirection-to-write').attr('href',writeUrl + 'improve-post/' + `${result.id}` + '/', '_blank'); $('.create-improvement-redirection-to-write')[0].click(); }, error:function(e) { showErrorMessage(e.responseJSON,e.status); }, }); });

Continue without supporting

`; $('body').append(adBlockerModal); $('body').addClass('body-for-ad-blocker'); const modal = document.getElementById("adBlockerModal"); modal.style.display = "block"; } function handleAdBlockerClick(type){ if(type == 'disabled'){ window.location.reload(); } else if(type == 'info'){ document.getElementById("ad-blocker-div").style.display = "none"; document.getElementById("ad-blocker-info-div").style.display = "flex"; handleAdBlockerIconClick(0); } } var lastSelected= null; //Mapping of name and video URL with the index. const adBlockerVideoMap = [ ['Ad Block Plus','https://media.geeksforgeeks.org/auth-dashboard-uploads/abp-blocker-min.mp4'], ['Ad Block','https://media.geeksforgeeks.org/auth-dashboard-uploads/Ad-block-min.mp4'], ['uBlock Origin','https://media.geeksforgeeks.org/auth-dashboard-uploads/ub-blocke-min.mp4'], ['uBlock','https://media.geeksforgeeks.org/auth-dashboard-uploads/U-blocker-min.mp4'], ] function handleAdBlockerIconClick(currSelected){ const videocontainer = document.getElementById('ad-blocker-info-div-gif'); const videosource = document.getElementById('ad-blocker-info-div-gif-src'); if(lastSelected != null){ document.getElementById("ad-blocker-info-div-icons-"+lastSelected).style.backgroundColor = "white"; document.getElementById("ad-blocker-info-div-icons-"+lastSelected).style.borderColor = "#D6D6D6"; } document.getElementById("ad-blocker-info-div-icons-"+currSelected).style.backgroundColor = "#D9D9D9"; document.getElementById("ad-blocker-info-div-icons-"+currSelected).style.borderColor = "#848484"; document.getElementById('ad-blocker-info-div-name-span').innerHTML = adBlockerVideoMap[currSelected][0] videocontainer.pause(); videosource.setAttribute('src', adBlockerVideoMap[currSelected][1]); videocontainer.load(); videocontainer.play(); lastSelected = currSelected; }