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

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

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.

  • A 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.

    See “Transform Input/Output Strings for ARRAY/VARRAY Types” on page 745 for the formatting of the transformed output.

  • A 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 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.

  • 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 aborts the request and returns an error to the requestor.

  • A 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.

  • 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
  • A one‑dimensional ARRAY/VARRAY type casting.
  • 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.