System‑Generated Default Functionalities For One Dimensional ARRAY and VARRAY UDTs
Once you have created a one‑dimensional 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 one‑dimensional ARRAY/VARRAY type. The one‑dimensional 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 one‑dimensional 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 one‑dimensional ARRAY or VARRAY type you create.
Teradata Database sets all the elements of the one‑dimensional ARRAY/VARRAY 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 one‑dimensional ARRAY/VARRAY type has been created without specifying the DEFAULT NULL option.
Teradata Database stores the element values of a one‑dimensional ARRAY/VARRAY sequentially, so you must pass the parameters to the constructor in sequential order. The constructor initializes the elements of a one‑dimensional ARRAY/VARRAY starting with the first element, moving from left to right.
Although there normally is an upper limit of 128 parameters for a constructor method, the number of parameters you can specify for a one‑dimensional ARRAY/VARRAY constructor method is only limited by the maximum number of elements that you have defined for the one‑dimensional ARRAY/VARRAY type.
If a one‑dimensional ARRAY/VARRAY has n total elements, and you pass more than n parameters to the constructor, Teradata Database aborts the request and returns an error to the requestor.
Teradata Database provides very basic ordering functionality for a newly created one‑dimensional ARRAY/VARRAY type.
Teradata Database provides this ordering functionality to avoid hashing issues and to enable one‑dimensional ARRAY/VARRAY types to be allowed as columns in SET tables.
You cannot make relational comparisons of one‑dimensional ARRAY/VARRAY values.
Teradata Database provides casting functionality for a one‑dimensional ARRAY/VARRAY type. The system provides two autogenerated casts for all one‑dimensional ARRAY/VARRAY types.
The format of the VARCHAR string as both the case source and target value is the same as the format 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 the elements of two one‑dimensional ARRAY/VARRAY types.
See the Teradata Developer Exchange, DevX, at http://developer.teradata.com for a sample casting UDF that you can download.