15.00 - Function of UDT Transforms - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Function of UDT Transforms

The CREATE TRANSFORM statement associates a transform group with a UDT. You do not need to create a transform group for any Period or Geospatial data types.

There can be only one transform group per UDT, and there must be a transform group for each UDT you develop. Note that the system defines a transform group for you when you create a new distinct UDT by default, but you must explicitly define a transform group for all new structured UDTs.

A UDT must have its transform functionality completely defined to be used as the column type of any table, otherwise an attempt to create or alter a table to include a column having the UDT type aborts and the system returns an error to the requestor.

The transform group of a UDT performs the following mappings:

  • From a particular Teradata Database UDT to the server form of a particular predefined client data type.
  • A routine that performs this transform is referred to as a fromsql transform routine.

  • From a predefined client data type to a particular UDT in Teradata Database.
  • A routine that performs this transform is referred to as a tosql transform routine.

    Such a pair of transform mappings is called a transform group. A transform group defines a single tosql/fromsql pair. There must be one, and only one, transform group per UDT.

    You can perform a valid CREATE TRANSFORM request that associates only a tosql routine or only a fromsql routine with a UDT, but you must define complete transform functionality for a UDT before you can specify it as the column data type of any table. If you do not, the system aborts the ALTER TABLE or CREATE TABLE request and returns an error to the requestor.

     

    FOR this type of UDT …

    THE transform group functionality …

    one‑dimensional and multidimensional ARRAY and VARRAY

    is generated automatically by Teradata Database.

    The format of the transformed output in a VARCHAR string is a string of each array element value, referred to as the transformed value string, separated by a comma and delimited by parentheses as indicated below. Assuming the array has n elements:

    (<element_1>,<element_2>, … <element_n>)

    The following rules apply.

  • BLOB, CLOB, and Geospatial element types are not supported.
  • If the element type is not CHARACTER, VARCHAR, or a UDT with a CHARACTER or VARCHAR attribute, the definition of the array constructor/transformed value string is VARCHAR(64000) CHARACTER SET LATIN.
  • If the size of the transformed value string is greater than 64k when the array type is being created, the request aborts and Teradata Database returns an error to the requestor.

  • If the element type is CHARACTER, VARCHAR, or a UDT with a CHARACTER or VARCHAR attribute, the definition of the array transformed value string is VARCHAR(max) of the same character set as its element type.
  • For the VARCHAR case, max signifies the largest numeric value possible for the size of a VARCHAR in the current character set.

    For a Latin character set, this is 64 KB.

    If the size of the transformed value string is greater than the value of max when the ARRAY type is being created, the request aborts and Teradata Database returns an error to the requestor.

  • The system does not report any uninitialized elements. If an element value has been initialized, then all of the elements before it will have also been initialized either to null or to a value.
  • Overflow avoidance.
  • The size of an array transformed value string must be within the size limit of a VARCHAR, CHARACTER, and CLOB data types, particularly VARCHAR data type).

    If the size of an array transformed value string is bigger than the size limit of a VARCHAR variable when the ARRAY type is being created, Teradata Database aborts the request and returns an error to the requestor.

  • A null element must be indicated by a NULL literal. If the server character set is other than Latin, the null element is indicated by the corresponding encoding of a NULL literal in the server character set. The same applies on a null structured UDT value or a structured UDT with a null attribute.
  • one‑dimensional and multidimensional ARRAY and VARRAY (continued)

  • Teradata Database ignores any pad, new line, or tab characters whether they precede the comma or follow it. The same is true if such characters precede the last APOSTROPHE character or follow the first APOSTROPHE character.
  • If the element type is CHARACTER, VARCHAR, or UDT with CHARACTER or VARCHAR attributes, and if ARRAY/VARRAY elements contain many embedded APOSTROPHE characters, this requires an extra APOSTROPHE character to distinguish the embedded APOSTROPHE character. This limits the total size of the transform string that can be output when selecting the ARRAY/VARRAY column because it counts the embedded APOSTROPHE characters.

    If your ARRAY/VARRAY elements contain embedded APOSTROPHE characters, they are also be output when the ARRAY/VARRAY column is selected in transforms ON mode, using the fromsql transform.

    In the worst case, a string with all APOSTROPHE characters embedded, the max transform string is reduced by half. Therefore, if the total size of the transform string that could be generated for an ARRAY/VARRAY type exceeds the maximum row size, the CREATE TYPE request for that ARRAY/VARRAY type aborts and Teradata Database returns an error to the requestor.

    distinct

    is generated automatically by Teradata Database.

    If the system-generated transform group semantics is adequate, you need not define explicit tosql and fromsql transform functionality using the CREATE TRANSFORM statement.

    If your applications require different or richer transform semantics, then you can specify explicit transform group functionality using CREATE TRANSFORM.

    structured

    must be defined explicitly using the CREATE TRANSFORM statement.

    The system invokes transforms implicitly whenever UDT data is imported to or exported from Teradata Database.

    The system does not permit you to specify a transform group for the UDT parameter set of external routines. Instead, when a process invokes an external routine, the system passes a UDT handle to the external routine argument rather than passing the UDT value to it directly. The external routine can use this UDT handle to get or set the value of a UDT argument by means of a set of library functions provided by Teradata (see SQL External Routine Programming for details).

    The predefined type that is specified in the transform group routines of a UDT is referred to as the external type of the UDT. The external type is always in Teradata server format, meaning that the normal client format‑to‑Teradata Database format transformations continue to take place in addition to UDT‑specific tosql and fromsql operations.

    For a tosql operation, this means the following actions occur in the sequence indicated.

    1 The system transforms the external type of the UDT from its client format to its Teradata Database format.

    2 The system performs the tosql routine.

    For a fromsql operation, this means the following actions occur in the sequence indicated:

    1 The system performs the fromsql routine to transform the UDT to its external type Teradata Database form.

    2 The system transforms the external type from its Teradata Database format to its client format.