# %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;

```

```output

+---+-------+
| 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 ;

```

```output

+-----+
| 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 ;

```

```output

+------+
| 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);

```

```output

+---------------+
| 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.

***

Modified at: March 8, 2024

Source: [/cloud/reference/sql-reference/procedural-sql-reference/rowtype-and-type/](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/rowtype-and-type/)

(An index of the documentation is available at /llms.txt)