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 MemSQL 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".

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!