System-Generated Default Functionalities For One Dimensional ARRAY and VARRAY UDTs - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™

When you create a one-dimensional ARRAY/VARRAY UDT, Vantage 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

Vantage 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.

Vantage sets all the elements of the one-dimensional ARRAY/VARRAY to an uninitialized state. This is different from setting the elements null.

The system returns an error to the requestor if any element is accessed from the ARRAY/VARRAY.

Vantage 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.

Vantage 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, the system returns an error to the requestor.

One-Dimensional ARRAY/VARRAY Type Ordering

Vantage provides very basic ordering functionality for a newly created one-dimensional ARRAY/VARRAY type.

Vantage 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.

You cannot use any of the relational comparison operators on one-dimensional ARRAY data, nor can you specify a one-dimensional ARRAY/VARRAY column in any of the following SQL DML clauses:
  • 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

Vantage 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. 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.