15.00 - ARRAY Constructor Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

ARRAY Constructor Expression

Purpose  

Constructs a new instance of an ARRAY type and initializes it using the ARRAY constructor method or function.

Syntax  

where:

 

Syntax element…

Specifies…

array_name

the name of the ARRAY data type that you want to initialize.

element_expression

an expression that evaluates to a literal value. The data type of the value must be the same as the element type of the array_name array.

element_expression can be repeated n times, where n is the declared size of the array_name array. The maximum limit for n is 2559.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Argument Type and Rules

Expressions passed to the ARRAY constructor must be able to be implicitly converted to the data type of the element type of the array_name array. Existing implicit CAST functionality, such as casting from CHAR to Timestamp or Timestamp to DATE, is supported for the ARRAY constructor expression.

The ARRAY constructor expression accepts zero or more arguments. If there are zero arguments, the ARRAY constructor initializes an empty ARRAY type value and sets all the element values of the array to an uninitialized state if the ARRAY data type was created without the DEFAULT NULL clause. If the ARRAY data type was created with a DEFAULT NULL clause, then the ARRAY constructor initializes all the element values of the array to null. Note that if you try to access an array element that is in an uninitialized state, you will get an error.

You can pass up to n arguments to the ARRAY constructor expression, where n is the declared size of the ARRAY data type. For a one-dimensional ARRAY type, the ARRAY constructor fills the elements of the array with the argument values sequentially starting from the first element, in the order that the arguments are passed in. For a multidimensional ARRAY type, pass the arguments to the ARRAY constructor expression in row-major order because elements in a multidimensional array are filled in row-major order.

If the number of arguments are less than the number of elements in the array, then the remaining elements for which an argument value is not provided are set to an uninitialized state. However, if the ARRAY data type was created with the DEFAULT NULL clause, then those elements are set to null.

A constructor method for a UDT may not accept more than 128 arguments; however, this limit does not apply to ARRAY data types. The number of arguments that you can specify for the ARRAY constructor is limited only by the maximum number of elements that you can define for the ARRAY type or 2559, whichever comes first.

Note that if the element type of the array is a variable-length type, and if the value passed to initialize the element is larger than the maximum size of the element type, Teradata Database automatically truncates the passed value to the maximum size of the element type. This truncation behavior occurs with transaction processing in Teradata session mode only. In ANSI session mode, Teradata Database does not truncate the passed value, and returns an error instead. This behavior is identical to that of distinct and structured UDTs.

Invoking the ARRAY Constructor Expression Without the NEW Keyword

You can invoke the ARRAY constructor expression without using the NEW keyword. This is compatible with Oracle syntax. When invoked without the NEW keyword, Teradata Database handles the expression like a UDF expression. This restricts the places an ARRAY constructor expression can be used to the same usage as a scalar UDF. This means that in some scenarios, the ARRAY constructor expression with NEW keyword can be used in an SQL statement where the ARRAY constructor expression without NEW keyword cannot. For example, you can use the ARRAY constructor expression with NEW keyword in the DEFAULT clause of a CREATE TABLE statement. However you cannot use the ARRAY constructor expression without NEW keyword in the DEFAULT clause.

Additionally, since the ARRAY constructor expression without NEW keyword is treated like a scalar UDF, the existing search order rules for UDFs will apply in this case. Teradata Database searches for UDFs in the following databases in the following order:

1 Default database

2 SYSLIB database

3 TD_SYSFNLIB database

4 SYSUDTLIB database

If a UDF exists in the default database, SYSLIB, or TD_SYSFNLIB and has the same name as a defined ARRAY data type, and if you invoke the ARRAY constructor expression without the NEW keyword, then Teradata Database invokes the UDF instead of the ARRAY constructor. To avoid this conflict, use the NEW keyword when invoking the ARRAY constructor.

Example

Consider the following 1-D ARRAY data type:

   CREATE TYPE phonenumbers_ary AS CHAR(10) ARRAY[5];

The following shows an ARRAY constructor expression that sets the first two elements in phonenumbers_ary to the provided values, and the rest of the elements in the array are set to an uninitialized state.

   NEW phonenumbers_ary('5551234567', '8585551234')

or

   phonenumbers_ary('5551234567', '8585551234')