CREATE_ARRAY

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, given myarray ARRAY(INT) = CREATE_ARRAY(3);, each of the three element values in the array will be NULL. Additionally, if the element data type has a NULL modifier, such as myarray ARRAY(INT NULL) = CREATE_ARRAY(3), then each of the three element values in the array will be NULL.

• 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, given myarray ARRAY(INT NOT NULL) = CREATE_ARRAY(3), each of the three element values will be 0, which is the default value for the INT data type. For a complete list of default values for each data type, see Array Default Values.

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 ;

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.

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 ;

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 ;

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

• ARRAY