System‑Generated Default Functionalities For Multidimensional ARRAY and VARRAY UDTs
Once you have created a multidimensional ARRAY/VARRAY UDT, Teradata Database automatically generates the following additional UDT‑related functionality for you.
This is the fromsql and tosql functionality associated with the transform of a multidimensional ARRAY/VARRAY type. The multidimensional ARRAY/VARRAY values are transformed to/from a VARCHAR(length) value, where length depends on the element type and the total number of elements defined in the multidimensional ARRAY/VARRAY.
See “Transform Input/Output Strings for ARRAY/VARRAY Types” on page 745 for the formatting of the transformed output.
Teradata Database automatically generates a default constructor function and an additional constructor method for each multidimensional ARRAY/VARRAY type you create.
Equation element …
Specifies the …
[ lower_bound : upper_bound ]
boundary range for the multidimensional array, where both lower_bound and upper_bound are signed INTEGER values.
[ maximum_size ]
maximum size of the multidimensional array, which cannot exceed the maximum size of a Teradata Database row, or 64,256 bytes.
Teradata Database sets all the elements of the multidimensional ARRAY to an uninitialized state. This is different from setting the elements null.
Teradata Database aborts the request and returns an error to the requestor if any element is accessed from the ARRAY/VARRAY.
Teradata Database sets all of the elements to an uninitialized state only if the multidimensional ARRAY/VARRAY type has been created without specifying the DEFAULT NULL option.
Teradata Database stores the element values of a multidimensional ARRAY/VARRAY in row‑major order. Row‑major means that the first dimension, which is leftmost in the scope specification, is the major dimension, and as you move toward the last dimension, the dimensions become less and less major. This applies to arrays of an arbitrary number of dimensions. Most modern computer languages use row‑major storage.
The constructor method initializes the elements of the multidimensional ARRAY/VARRAY starting with the first element as defined in the scope specification and proceeds in row‑major order through the elements.
Although there normally is an upper limit of 128 parameters for a constructor method, the number of parameters you can specify for a multidimensional ARRAY/VARRAY types is only limited by the maximum number of elements that can be defined for the multidimensional ARRAY/VARRAY type. If a multidimensional ARRAY/VARRAY has n total elements, and you pass more than n parameters to the constructor method, Teradata Database aborts the request and returns a message to the requestor.
Teradata Database provides basic ordering functionality for a newly created multidimensional ARRAY/VARRAY type.
Teradata Database provides this ordering functionality to avoid hashing issues and to enable multidimensional ARRAY/VARRAY types to be allowed as columns in SET tables.
You cannot make relational comparisons of multidimensional ARRAY/VARRAY values, though you can make relational comparisons of the individual elements of a multidimensional ARRAY/VARRAY type. See SQL Functions, Operators, Expressions, and Predicates for details about how to do this.
You cannot specify a multidimensional ARRAY/VARRAY column in any of the following SQL DML clauses.
Teradata Database provides casting functionality for a multidimensional ARRAY/VARRAY type. The system provides two autogenerated casts for all multidimensional ARRAY/VARRAY types.
The format of the VARCHAR string as both the cast source and target value is the same as that of the tosql and fromsql transforms. See “Transform Input/Output Strings for ARRAY/VARRAY Types” on page 745 for more information about the formats of these strings.
You can also create your own UDFs to perform casting operations between all or a subset of the elements of two multidimensional ARRAY/VARRAY types.
See the Teradata Developer Exchange, DevX, at http://developer.teradata.com for a sample UDF to cast multidimensional ARRAY/VARRAY data types that you can download.