# RECORD

SingleStore Procedural SQL (PSQL) supports the `RECORD` data type, which is an encapsulated group of named fields.

Records support an unlimited number of fields, and each field’s data type can be different. Records in SingleStore are conceptually similar to a struct data type in other programming languages, such as C or C++.

Records can be declared in the body of a user-defined function (UDF) or stored procedure (SP), and can be used in assignment statements or accessed in expressions. In addition, records can be specified as an input parameter or return type for UDFs or SPs.

## Syntax

The following syntax declares a new record.

```
variable_name RECORD(field_definition [, ...]) [data_type_modifier]
      { ; | initialization_clause }

  field_definition:
    field_name data_type [data_type_modifier]

```

## Arguments

**variable\_name**

The name of the record variable.

**field\_name**

The name of the field.

**data\_type**

Any scalar-valued or non-scalar-valued data type, expect for `QUERY` types. For a complete list of data types, see the [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) topic.

**data\_type\_modifier**

The optional modifier for the data type, such as `NULL` or `NOT NULL`. There are two kinds of optional modifiers in a record declaration: the modifier that applies to the record type itself, and the modifier that applies to each field in the record. By default, a record type is `NOT NULL`.

**initialization\_clause**

An optional clause that initializes the record.

## Remarks

After a record variable has been declared, new fields cannot be added to the record and existing fields cannot be removed from the record. In addition, the data type for a given field cannot be changed to a different type.

When calling a function or procedure, input parameters and return types are always passed by value. This behavior applies to all types, including records.

## Record Declaration and Initialization

Record types are declared in a function or procedure as either an input parameter or as a variable in the `DECLARE` block. For example:

```sql
CREATE FUNCTION my_function(rec1 RECORD(y INT, z INT)) RETURNS INT AS
DECLARE
  -- Declares a new uninitialized record
  rec2 RECORD(a VARCHAR(255), b INT, c DATE);
  -- Declares an initialized record using ROW()
  rec3 RECORD(d INT, e VARCHAR(255)) = ROW(1, "Example");
  ...

```

You can initialize a record in the `DECLARE` block using the `ROW()` built-in function, which creates a new record and assigns the value for each field. To initialize a record variable at the time of declaration, use the `=` or `:=` assignment operators or the `DEFAULT` keyword following the variable name and type. For more information, see the ROW() topic.

> **⚠️ Warning**: You cannot write to `RECORD` fields within SQL statements, for example the `SET` clause of an `UPDATE` statement.

The following examples demonstrate how to initialize a record:

```sql
rec RECORD(a VARCHAR(255), b INT) = ROW("Example", 1);

```

```sql
rec RECORD(a INT, b INT, c INT) DEFAULT ROW(1, 2, 3);

```

```sql
rec RECORD(a VARCHAR(255), b INT) = NULL;

```

## Field Assignment and Access

To assign the value of a record’s fields, use the following notation:

```
<record-name>.<field-name> = <value>;

```

For example, consider the following declared record variable:

```sql
rec RECORD(a INT, b VARCHAR(255));

```

To assign the value of the `rec` variable’s fields, use two assignment expressions as shown below:

```sql
rec.a = 100;
rec.b = "Example";

```

Fields are accessed using similar syntax, using the notation `<record-name>.<field-name>`. Consider the following example:

```sql
...
DECLARE
  i INT;
  s VARCHAR(255);
  rec RECORD(a INT, b VARCHAR(255)) = ROW(100, "Example");
BEGIN
  i = rec.a;
  s = rec.b;
END
...

```

In the example above, the value of `i` becomes `100`, and the value of `s` becomes `"Example"`.

## Accessing Records inside Stored Procedures

The notation `r.x` can be used as a reference to a table, view, CTE, or record. SingleStore prioritizes records over tables when referenced inside stored procedures. If a record variable and a table have the same name, and you specify the identifier for the table/record inside a stored procedure, SingleStore references the record variable. Although, if the operation specified in a DML query cannot be performed on the record, it throws an error. An exception to this behavior is a DDL query that references a table or a view. In this case, the specified table/view will take precedence over the record.

Consider the following examples. In the following code block, the `ECHO SELECT` statement returns `NULL` because it queries the record `emp` instead of the table (that has the same name).

```sql
CREATE TABLE emp (ERoll INT, ECode INT);
INSERT INTO emp VALUES (100,441);

DELIMITER //

CREATE OR REPLACE PROCEDURE chk() AS
DECLARE emp RECORD(ERoll INT);
BEGIN
  ECHO SELECT emp.ERoll AS "ERoll";
END //

DELIMITER ;

CALL chk();

```

```output

+-------+
| ERoll |
+-------+
|  NULL |
+-------+

```

The `UPDATE` statement in the following example returns an error, because you cannot perform an update on a record variable.

```sql
DELIMITER //

CREATE OR REPLACE PROCEDURE conflicts() RETURNS VOID AS
DECLARE emp RECORD(ERoll INT NULL, ECode INT);
BEGIN
  UPDATE emp SET ECode = emp.ERoll WHERE ERoll = emp.ECode;
END //

DELIMITER ;

CALL conflicts();

```

The `TRUNCATE TABLE` statement in the following example clears all the rows from the `emp` table, because DDL statements prioritize tables/views over records.

```sql
DELIMITER //

CREATE OR REPLACE PROCEDURE clean() RETURNS VOID AS
DECLARE emp RECORD(ERoll INT, ECode INT);
BEGIN
  TRUNCATE TABLE emp;
END //

DELIMITER ;

CALL clean();

```

## Using `RECORD` inside SQL statements

The following examples demonstrate the usage of `RECORD` elements inside SQL queries.

> **⚠️ Warning**: `RECORD` fields cannot be accessed directly inside a dynamic SQL statement.

## Accessing `RECORD` fields in `INSERT` statements

```sql
CREATE TABLE t3 (a INT, b INT);
INSERT INTO t3 VALUES(1, 2);

DELIMITER //

CREATE OR REPLACE PROCEDURE double_t() AS
DECLARE
  q  QUERY(i INT, j INT) = SELECT * FROM t3;
BEGIN
  FOR r IN COLLECT(q) LOOP
    INSERT INTO t3 VALUES (r.i, r.j);
  END LOOP;
END //

DELIMITER ;

CALL double_t();

SELECT * FROM t3;

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    2 |
+------+------+

```

## Accessing `RECORD` fields in `UPDATE` statements

```sql
CREATE TABLE t3 (a INT, b INT, c INT);
INSERT INTO t3(a,b) VALUES (1, 2);

DELIMITER //

CREATE OR REPLACE PROCEDURE update_st() AS
DECLARE
    r RECORD(a int, b int, c int) = ROW(1, 2, 3);
BEGIN
  UPDATE t3 SET c = r.c*10 WHERE a = r.a;
END //

DELIMITER ;

CALL update_st();

SELECT * FROM t3;

```

```output

+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |   30 |
+------+------+------+

```

## Accessing `RECORD` fields in `DELETE` statements

```sql
CREATE TABLE t3 (a INT, b INT, c INT);
INSERT INTO t3(a,b) VALUES (1, 2);
INSERT INTO t3(a,b) VALUES (5, 6);
SELECT * FROM t3;

```

```output

+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 | NULL |
|    5 |    6 | NULL |
+------+------+------+

```

```sql
DELIMITER //

CREATE OR REPLACE PROCEDURE del_st() AS
DECLARE
    r RECORD(a int, b int, c int) = ROW(1, 2, 3);
BEGIN
  DELETE FROM t3 WHERE c = r.a;
END //

DELIMITER ;

CALL del_st();

SELECT * FROM t3;

```

```output

+------+------+------+
| a    | b    | c    |
+------+------+------+
|    5 |    6 | NULL |
+------+------+------+

```

## Accessing `RECORD` fields in `SELECT` statements

```sql
DELIMITER //

CREATE OR REPLACE PROCEDURE prec() AS
DECLARE
  r RECORD(a INT, b INT) = ROW(1,2);
BEGIN
  ECHO SELECT r.a AS x, r.b AS y;
END //

DELIMITER ;

CALL prec();

```

```output

+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
+------+------+

```

## Accessing `RECORD` fields inside a `WHERE` clause

```sql
CREATE TABLE video(vid INT, title VARCHAR(30));

INSERT INTO video VALUES (1, "Hercules");
INSERT INTO video VALUES (2, "Incredibles");

CREATE TABLE play(user VARCHAR(30), vid INT, played_at DATETIME);

INSERT INTO play VALUES("Jill", 1, "2018-07-15 20:00:00");
INSERT INTO play VALUES("Rick", 2, "2018-07-15 20:01:00");
INSERT INTO play VALUES("Jane", 1, "2018-07-15 20:02:00");
INSERT INTO play VALUES("Bob", 2, "2018-07-15 20:03:00");

DELIMITER //

CREATE or REPLACE PROCEDURE tt(search_video_id INT) AS
DECLARE
  r RECORD(a int, b int, c int) = ROW(1,2,3);
BEGIN
  ECHO SELECT * FROM video, play WHERE video.vid = search_video_id and play.vid = r.b;
END //

DELIMITER ;

CALL tt(1);

```

```output

+------+----------+------+------+---------------------+
| vid  | title    | user | vid  | played_at           |
+------+----------+------+------+---------------------+
|    1 | Hercules | Rick |    2 | 2018-07-15 20:01:00 |
|    1 | Hercules | Bob  |    2 | 2018-07-15 20:03:00 |
+------+----------+------+------+---------------------+

```

## Passing a `RECORD` to a function

The following example demonstrates how to pass a record as an input parameter to a function:

```sql
DELIMITER //

CREATE FUNCTION rec_copy_example(rec1 RECORD(a INT, b INT))
RETURNS VARCHAR(255) AS
DECLARE
  rec2 RECORD(a INT, b INT) = rec1;
  result VARCHAR(255) = "";
BEGIN
  rec2.a = 100;
  result = CONCAT("rec1.a = ", rec1.a, "; rec2.a = ", rec2.a);
  RETURN result;
END //

DELIMITER ;

SELECT rec_copy_example(ROW(1,2));

```

```output

+----------------------------+
| rec_copy_example(ROW(1,2)) |
+----------------------------+
| rec1.a = 1; rec2.a = 100   |
+----------------------------+
1 row in set (0.19 sec)

```

**Related Topics**

* [ROW](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/row.md): The `ROW()` function constructs a `RECORD` object

***

Modified at: June 5, 2023

Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/record/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/record/)

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