%ROWTYPE and %TYPE

The %ROWTYPE attribute designates a record type that represents a table row and the %TYPE attribute designates the type of a referenced scalar object, such as another variable or column. When using these attributes for variable declaration, you don't have to know the exact type of the referenced object.

This can make it easier to program stored procedures and anonymous code blocks because you don't have to write as much code. In addition, your code will automatically adjust to changes in the source object's data type, making it easier to maintain and evolve your applications.

Syntax

%ROWTYPE attribute:

SQL

variable_name  \table_name%ROWTYPE
variable_name QUERY LIKE table_name%ROWTYPE

%TYPE attribute:

SQL

variable_name {other_variable_name | table_name.column_name}%TYPE

Remarks

%ROWTYPE can be used with tables, views, and query objects.
User-Defined Functions (UDFs), Stored Procedures (SPs), and anonymous code blocks using %ROWTYPE and/or %TYPE attributes are associated with specific tables. Any alterations of an associated table's schema trigger a recompilation of the UDF, SP, or anonymous code block upon the next execution. The %ROWTYPE and %TYPE attributes are defined during the initial function compilation. So altering the table during function or anonymous code block execution does not impact the variable, parameter, or return types; altered types will take effect on the next execution.

Examples

Shortening Code that Processes Wide Tables

In the following example, the data type of qry needs to be declared before it is assigned. Every field and its data type in the table table1, which qry represents, must be specified.

SQL

CREATE TABLE table1 (id INT, name TEXT);
INSERT INTO table1 VALUES (1, 'red'), (2, 'green'), (3, 'blue');

SQL

CREATE TABLE output_log(msg TEXT);

SQL

DELIMITER //
CREATE OR REPLACE PROCEDURE p() AS
DECLARE
    qry QUERY(id INT, name TEXT) = SELECT id, name FROM table1;
    arr ARRAY(RECORD(id INT, name TEXT));
BEGIN
    arr = COLLECT(qry);
    FOR x in arr LOOP
    INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
    END LOOP;
END //
DELIMITER ;

If table1 had hundreds of columns instead of 2, it would be tedious to have to list all of those columns along with their data type. The %ROWTYPE attribute can be used so all the columns don't have to be listed out when defining the QUERY and ARRAY data types.

SQL

DELIMITER //
CREATE OR REPLACE PROCEDURE p() AS
DECLARE
qry QUERY LIKE table1%ROWTYPE;
arr ARRAY(table1%ROWTYPE);
BEGIN
    qry = SELECT * FROM table1;
    arr = COLLECT(qry);
    FOR x in arr LOOP           
    END LOOP;
END //
DELIMITER ;

Allowing Anonymous Code Block or SP to Adapt to Schema Changes

SQL

CREATE TABLE IF NOT EXISTS test(a INT, b VARCHAR(255));

SQL

INSERT test VALUES (1,'red'),(2,'green');

SQL

CREATE VIEW testv AS SELECT a, b FROM test;

SQL

SELECT * FROM test;

+---+-------+
| a | b     |
+---+-------+
| 1 | red   |
| 2 | green |
+---+-------+

SQL

DELIMITER //
DO 
DECLARE
  qry QUERY LIKE testv%ROWTYPE;
  arr ARRAY(qry%ROWTYPE);
  r test%ROWTYPE;
  tot test.a%TYPE;
BEGIN
  qry = SELECT * FROM test ORDER BY a DESC; 
  arr = COLLECT(qry);
  r = arr[0];
  ECHO SELECT r.a, r.b;
  SELECT SUM(a) INTO tot FROM test;
  ECHO SELECT tot;
END //
DELIMITER ;

+-----+
| tot |
+-----+
|   3 |
+-----+

SQL

ALTER TABLE test ADD COLUMN c NUMERIC(18,2);
UPDATE test SET c = a;
ALTER TABLE test DROP COLUMN a;
ALTER TABLE test CHANGE c a;

The view must be recreated because the order of the columns in test has changed. %ROWTYPE depends on column order.

SQL

DROP VIEW testv;
CREATE VIEW testv AS SELECT * FROM test;

Rerun the anonymous code block above and it will pick up the new type.

SQL

DELIMITER //
DO 
DECLARE
  qry QUERY LIKE testv%ROWTYPE;
  arr ARRAY(qry%ROWTYPE);
  r test%ROWTYPE;
  tot test.a%TYPE;
BEGIN
  qry = SELECT * FROM test ORDER BY a DESC; 
  arr = COLLECT(qry);
  r = arr[0];
  ECHO SELECT r.a, r.b;
  SELECT SUM(a) INTO tot FROM test;
  ECHO SELECT tot;
END //
DELIMITER ;

+------+
| tot  |
+------+
| 3.00 | 
+------+

Allowing UDFs to Adapt to Table Column Type Changes

Consider a gaming application where a function performs logic on a points table to update users' points:

SQL

CREATE TABLE points(id INT, result INT);

This UDF code can adapt to changes in the types of columns of the points table. The declaration of b also illustrates how a variable type can be declared as the same type as another variable in the same function.

SQL

DELIMITER //
CREATE OR REPLACE FUNCTION update_pts(v points.result%TYPE) returns points.result%TYPE AS 
DECLARE
    r INT;
    b r%TYPE;
    result points.result%TYPE = 0;
  
BEGIN
   -- You can perform any additional logic here --
   RETURN result;
END
//
DELIMITER ;

SQL

SELECT update_pts(1);

+---------------+
| update_pts(1) |
+---------------+
|             0 |
+---------------+

In summary, %ROWTYPE and %TYPE allow several ways to shorten your code and make it adapt to schema changes automatically. Use it for UDFs, SPs, or anonymous code blocks with fewer lines of code and reduce PSQL code maintenance requirements.