SingleStore (MemSQL) Procedural SQL (MPSQL) 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 DB 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 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:

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.

The following examples demonstrate how to initialize a record:

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

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

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:

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

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

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

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

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

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 ERoll AS "ERoll" from emp;
END //
DELIMITER ;
CALL chk();
****
+-------+
| ERoll |
+-------+
|  NULL |
+-------+

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

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.

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.

Accessing RECORD fields in INSERT statements

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;
****
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    2 |
+------+------+

Accessing RECORD fields in UPDATE statements

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;
****
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |   30 |
+------+------+------+

Accessing RECORD fields in DELETE statements

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;
****
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 | NULL |
|    5 |    6 | NULL |
+------+------+------+

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;
****
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    5 |    6 | NULL |
+------+------+------+

Accessing RECORD fields in SELECT statements

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();
****
+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
+------+------+

Accessing RECORD fields inside a WHERE clause

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

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));
****
+----------------------------+
| rec_copy_example(ROW(1,2)) |
+----------------------------+
| rec1.a = 1; rec2.a = 100   |
+----------------------------+
1 row in set (0.19 sec)

Related Topics

• ROW: The ROW() function constructs a RECORD object

Was this Article Helpful?

Thanks For Your Feedback!