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

Record Example

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 ;

memsql> 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!