You are viewing an older version of this section. View current production version.
CREATE_ARRAY
Creates a new array of the specified length that is initialized with default values for the array’s inferred data type.
Syntax
CREATE_ARRAY(length_expression)
Arguments
length_expression
An expression that evaluates to an integer value, which is used to specify the length of the array. If the expression does not evaluate to an integer or evaluates to a negative integer, an exception will be thrown.
Remarks
The CREATE_ARRAY()
function uses data type inference to determine the appropriate type for each element in the array. The function can be used in any expression where the data type of the array can be inferred, such as:
- In assignment statements, such as assigning the value of an array variable
- As an input parameter to functions that accept an array type
- In a
RETURN
statement for a function that returns an array type - In expressions that use a valid operator for an array type, such as concatenation (
+
) or type casting (:>
)
See the Examples section for examples of these expressions.
When CREATE_ARRAY()
is executed, a new array is initialized and each element is given one of the following default values:
- If the array’s element data type is specified without a data type modifier, the default element values will be
NULL
for each index. For example, givenmyarray ARRAY(INT) = CREATE_ARRAY(3);
, each of the three element values in the array will beNULL
. Additionally, if the element data type has aNULL
modifier, such asmyarray ARRAY(INT NULL) = CREATE_ARRAY(3)
, then each of the three element values in the array will beNULL
. - If the array’s element data type is specified with a
NOT NULL
modifier, the element values will be assigned the appropriate default value for the data type. For example, givenmyarray ARRAY(INT NOT NULL) = CREATE_ARRAY(3)
, each of the three element values will be0
, which is the default value for theINT
data type. For a complete list of default values for each data type, see the Array Default Values section of the ARRAY topic.
Examples
The following examples demonstrate how the type of an array can be inferred depending on the usage of CREATE_ARRAY()
.
Example: Variable Assignment
The following snippet from a function creates a new integer array and assigns it to a variable:
DELIMITER //
CREATE FUNCTION example_func() RETURNS INT AS
DECLARE
a ARRAY(INT) = CREATE_ARRAY(10);
BEGIN
...
END //
DELIMITER ;
Example: Input Parameter
The following example demonstrates a function that accepts an array as an input parameter and returns its length.
DELIMITER //
CREATE OR REPLACE FUNCTION get_length(a ARRAY(INT)) RETURNS INT AS
BEGIN
RETURN LENGTH(a);
END //
DELIMITER ;
memsql> SELECT get_length(CREATE_ARRAY(10));
+------------------------------+
| get_length(CREATE_ARRAY(10)) |
+------------------------------+
| 10 |
+------------------------------+
1 row in set (0.07 sec)
When this function is called by passing CREATE_ARRAY()
as the input parameter, the type of the new array is inferred from the function definition’s input parameter type. In this case, the type is an integer array.
Example: RETURN Statement
The following example demonstrates two functions: one that simply creates a new array and returns it, and another which returns the size of the array. Note that you cannot directly return the result of create_array_of_size()
in a SELECT
statement because returning array type values to the client is not supported.
DELIMITER //
CREATE OR REPLACE FUNCTION create_array_of_size(a INT) RETURNS ARRAY(INT) AS
BEGIN
RETURN CREATE_ARRAY(a);
END //
CREATE OR REPLACE FUNCTION get_length(b ARRAY(INT)) RETURNS INT AS
BEGIN
RETURN LENGTH(b);
END //
DELIMITER ;
When the create_array_of_size()
function is called, the returned array’s type is inferred from the function definition’s return type, which is an integer array.
memsql> SELECT get_length(create_array_of_size(10));
+--------------------------------+
| get_length(get_array_size(10)) |
+--------------------------------+
| 10 |
+--------------------------------+
1 row in set (0.10 sec)
Example: Type Casting
The following example demonstrates creation of an array, where its type is inferred from a typecast.
DELIMITER //
CREATE OR REPLACE FUNCTION get_length(a INT) RETURNS INT AS
DECLARE
b INT;
BEGIN
b = LENGTH(CREATE_ARRAY(a) :> ARRAY(INT));
RETURN b;
END //
DELIMITER ;
memsql> SELECT get_length(10);
+----------------+
| get_length(10) |
+----------------+
| 10 |
+----------------+
1 row in set (0.12 sec)
Example: Array Concatenation
The following example demonstrates how to extend the length of an existing array by concatenating it with the result of CREATE_ARRAY()
.
DELIMITER //
CREATE OR REPLACE FUNCTION extend_array(a ARRAY(INT NOT NULL)) RETURNS ARRAY(INT NOT NULL) AS
DECLARE
c ARRAY(INT NOT NULL) = a;
BEGIN
c += CREATE_ARRAY(5);
RETURN c;
END //
CREATE OR REPLACE FUNCTION list_array_elements(a ARRAY(INT NOT NULL)) RETURNS VARCHAR(255) AS
DECLARE
result VARCHAR(255) = "";
BEGIN
IF LENGTH(a) = 0 THEN
RETURN "[]";
END IF;
result = CONCAT(result, "[", a[0]);
FOR i IN 1..LENGTH(a) - 1 LOOP
result = CONCAT(result, ", ", a[i]);
END LOOP;
result = CONCAT(result, "]");
RETURN result;
END //
CREATE OR REPLACE FUNCTION before_after_concat() RETURNS VARCHAR(255) AS
DECLARE
primes ARRAY(INT NOT NULL) = [2, 3, 5, 7, 11];
result VARCHAR(255);
BEGIN
result = CONCAT("Original: ", list_array_elements(primes));
primes = extend_array(primes);
result = CONCAT(result, "; Updated: ", list_array_elements(primes));
RETURN result;
END //
DELIMITER ;
memsql> SELECT before_after_concat();
+----------------------------------------------------------------------+
| before_after_concat() |
+----------------------------------------------------------------------+
| Original: [2, 3, 5, 7, 11]; Updated: [2, 3, 5, 7, 11, 0, 0, 0, 0, 0] |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
Related Topics