ARRAY
SingleStoreDB (MemSQL) Procedural SQL (MPSQL) supports the ARRAY
data type, which is a collection of elements that share the same data type.
Arrays 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, arrays can be specified as an input parameter or return type for UDFs or SPs.
Syntax
The following syntax declares a new array.
variable_name ARRAY(data_type [data_type_modifier]) [data_type_modifier] { ; | initialization_clause }
Arguments
variable_name
The name of the array variable.
data_type
Any scalar-valued or non-scalar valued data type, except 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 an array declaration: the modifier that applies to the array type itself, and the modifier that applies to each element in the array. By default, an array type is NOT NULL
.
initialization_clause
An optional clause that initializes the array. For more information, see Array Declaration and Initialization.
Remarks
Arrays use a zero-based index, where the first element in the array is at index 0
. Accordingly, the last element in an array has an index equal to the array’s length minus one. For example, if an array has been declared with the following values: myarray = ["quick","brown","fox"]
, then the first element is "quick"
at index 0
, and the last element is "fox"
at index 2
.
An exception will be thrown if you attempt to reference an element outside the bounds of an array.
Array elements cannot be used in DDL or DML statements. An array element can be assigned to a variable, and the variable can be used in a DDL or DML statement. See CREATE_ARRAY for information on how to assign an array element to a variable.
Arrays can be defined with an unlimited number of dimensions. See the Multidimensional Arrays section for more information.
Array Declaration and Initialization
Array 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(arr1 ARRAY(INT)) AS DECLARE -- Declares a new uninitialized array arr2 ARRAY(VARCHAR(255)); -- Declares a new array initialized with specific values arr3 ARRAY(INT) = [1, 2, 3]; ...
You can initialize an array in the DECLARE
block of a function or procedure using an assignment, either by using the DEFAULT
keyword or the =
or :=
assignment operators.
The following examples demonstrate how to initialize an array:
myarray ARRAY(INT) DEFAULT [1, 2, 3];
myarray ARRAY(INT) = [1, 2, 3];
The following example demonstrates how to initialize an array using the CREATE_ARRAY()
function:
myarray ARRAY(INT) = CREATE_ARRAY(3);
The following examples show how to declare and initialize arrays of other types:
my_varchar_array ARRAY(VARCHAR(255)) = ["one", "two", "three"]; my_double_array ARRAY(DOUBLE) = [1.1, 2.2, 3.3]; my_json_array ARRAY(JSON) = ['{"number": 1}', '{"number": 2}', '{"number": 3}']; my_multi_array ARRAY(ARRAY(INT)) = [[1, 2, 3], [4, 5, 6]];
A declared array can also be initialized using an assignment statement in the BEGIN ... END
block of a function or procedure. The following example returns the size of an initialized array:
DELIMITER // CREATE FUNCTION get_length() RETURNS INT AS DECLARE a ARRAY(INT); BEGIN a = [1,2,3]; RETURN LENGTH(a); END // DELIMITER ;
SELECT get_length(); **** +--------------+ | get_length() | +--------------+ | 3 | +--------------+ 1 row in set (0.10 sec)
Array Access and Assignment
To access the value of element i
in array a
, use the following notation:
a[i]
For example, consider the following declared array:
my_array ARRAY(VARCHAR(255)) = ["the", "quick", "brown", "fox"];
The last element in array my_array
can be accessed using the following expression:
my_array[3];
The expression above evaluates to fox
.
Array assignment is performed in a similar way. To assign a value to element i
of array a
, use an assignment statement such as:
a[i] = <value>;
For example, to assign dog
to element 3 in my_array
, use the following assignment statement:
my_array[3] = "dog";
Now the value of each element in my_array
becomes:
["the", "quick", "brown", "dog"]
Multidimensional Arrays
An ARRAY
can be declared with multiple dimensions. For example, the following syntax declares a multidimensional array:
my_array ARRAY(ARRAY(VARCHAR(255));
The following example declares a multidimensional array with an initial value:
my_array ARRAY(ARRAY(VARCHAR(255))) = [["the", "quick"], ["brown", "fox"]];
Multidimensional Array Access and Assignment
Multidimensional arrays are assigned and accessed using syntax similar to that used with a one-dimensional array, but by using another set of square brackets for each additional dimension. To access the value of element i
in array 0
of multidimensional array md
, use the following notation:
md[0][i]
Given a multidimensional array with values [["the", "quick"], ["brown", "fox"]]
, you can access the value of the last element in the first array using the following syntax:
element_value VARCHAR(255) = my_array[0][1];
The value of element_value
becomes quick
.
To assign the value of the last element in the last array:
my_array[1][1] = "dog";
The value of each element in my_array
becomes:
[["the", "quick"], ["brown", "dog"]]
You can replace an array-type element in a multidimensional array with another initialized array that uses the same element data type. For example, consider the following two array variables:
my_array ARRAY(ARRAY(VARCHAR(255))) = [["the", "quick"], ["brown", "fox"]]; replacement ARRAY(VARCHAR(255)) = ["the", "lazy"];
To replace the first array in my_array
with replacement
, i.e. replacing ["the", "quick"]
with ["the", "lazy"]
, simply set the element value as shown below:
my_array[0] = replacement;
You can try this out yourself using the complete example shown below:
DELIMITER // CREATE OR REPLACE FUNCTION md_replace_element() RETURNS VARCHAR(255) AS DECLARE my_array ARRAY(ARRAY(VARCHAR(255))) = [["the", "quick"], ["brown", "fox"]]; replacement ARRAY(VARCHAR(255)) = ["the", "lazy"]; result VARCHAR(255); BEGIN my_array[0] = replacement; result = array_as_string(my_array); RETURN result; END // CREATE OR REPLACE FUNCTION array_as_string(a ARRAY(ARRAY(VARCHAR(255)))) RETURNS VARCHAR(255) AS DECLARE result VARCHAR(255) = "Values: ["; BEGIN FOR i IN 0 .. LENGTH(a) - 1 LOOP result = CONCAT(result, "["); FOR j IN 0 .. LENGTH(a[i]) - 1 LOOP IF j < LENGTH(a[i]) - 1 THEN result = CONCAT(result, a[i][j], ", "); ELSE result = CONCAT(result, a[i][j]); END IF; END LOOP; IF i < LENGTH(a) - 1 THEN result = CONCAT(result, "], "); ELSE result = CONCAT(result, "]"); END IF; END LOOP; RETURN CONCAT(result, "]"); END // DELIMITER ;
This example returns the following result:
SELECT md_replace_element(); +-------------------------------------+ | md_replace_element() | +-------------------------------------+ | Values: [[the, lazy], [brown, fox]] | +-------------------------------------+ 1 row in set (1.07 sec)
Lastly, each array in a multidimensional array can be of a different length. For example:
my_array = [[1, 2], [1, 2, 3], [1, 2, 3, 4]];
Example
The following example function creates a multidimensional array and sums the contents of its elements:
DELIMITER // CREATE OR REPLACE FUNCTION md_array_sum() RETURNS INT AS DECLARE my_array ARRAY(ARRAY(INT)); sum INT = 0; BEGIN my_array = [[0, 100], [100, 0]]; FOR i IN 0 .. LENGTH(my_array) - 1 LOOP FOR j IN 0 .. LENGTH(my_array[i]) - 1 LOOP sum += my_array[i][j]; END LOOP; END LOOP; RETURN sum; END // DELIMITER ;
SELECT md_array_sum(); +----------------+ | md_array_sum() | +----------------+ | 200 | +----------------+ 1 row in set (0.00 sec)
Array Default Values
When an array is created with the CREATE_ARRAY()
function, each element in the array will be set to a default value. If the type is nullable, the default value will be NULL
. For types declared with the NOT NULL
modifier, each element in the newly created array is initialized with a default value that depends on the element data type for the array. The default values for each type are specified in the table below:
Data Type(s) | Default Value |
---|---|
Integer numbers, including |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Variable-length strings, including | Empty string |
| One ASCII space ( |
| One or more ASCII spaces ( |
| One or more ASCII zeroes ( |
|
|
|
|
|
|
| Each field within the record will be assigned their own default values depending on their data type. |