When you create a one-dimensional ARRAY/VARRAY UDT, Teradata Database automatically generates the following additional UDT-related functionality for you.
One-Dimensional Type Transform
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.
For the formatting of the transformed output, see Transform Input/Output Strings for ARRAY/VARRAY Types.
Constructor Function and Constructor Method
Teradata Database automatically generates a default constructor function and an additional constructor method for each one-dimensional ARRAY or VARRAY type you create.
The default constructor function takes no parameters and allocates a one-dimensional ARRAY/VARRAY type instance.
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 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.
The constructor method takes one or more parameters, up to the number of parameters as defined by the declared size of the one-dimensional ARRAY/VARRAY data type, and initializes each element of the one-dimensional ARRAY/VARRAY with the corresponding value that is passed to it.
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 returns an error to the requestor.
One-Dimensional ARRAY/VARRAY Type Ordering
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.
- INTERSECT set operator
- MINUS set operator
- UNION set operator
- DISTINCT operator
- WHERE clause
- ORDER BY clause
- GROUP BY clause
- HAVING clause
One-Dimensional ARRAY/VARRAY Type Casting
- VARCHAR to ARRAY/VARRAY
- ARRAY/VARRAY to VARCHAR
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. For more information about the formats of these strings, see Transform Input/Output Strings for ARRAY/VARRAY Types.
You can also create your own UDFs to perform casting operations between the elements of two one-dimensional ARRAY/VARRAY types.