17.10 - Enabling Full Functionality For a Distinct UDT - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

When you create a distinct UDT, Vantage creates all the objects or definitions listed as Yes under the Mandatory? column in the following table to enable the full functionality of the type.

Functionality Defining DDL Statement Purpose       Mandatory?
Definition of a structured UDT. CREATE TYPE (Structured Form) Declares the body and attributes of a structured UDT. Yes
Definition of an instance method. CREATE METHOD Declares any SQL-invoked instance methods to be associated with the structured UDT. No
Definition of the ordering for comparing structured UDT values. CREATE ORDERING Registers the UDF or method to be used as an ordering routine to compare the structured UDT value with other values. Yes
Declaration of the external ordering routine referenced by CREATE ORDERING.
  • CREATE FUNCTION

    or

  • CREATE METHOD
Declares the ordering routine referenced in the CREATE ORDERING definition. Yes
Transformation of a UDT between client and server data types in both directions. CREATE TRANSFORM Registers the UDF and UDF or method to be used as a tosql() and fromsql() transform routine to pass the structured UDF in both directions between the client and server. Yes
Declaration of the external transformation routine referenced by CREATE TRANSFORM.
  • CREATE FUNCTION

    or

  • CREATE METHOD
Declares the tosql() and fromsql() external routines referenced in the CREATE TRANSFORM definition. Yes
Registration of an external UDF or method casting routine. CREATE CAST Registers a UDF or method to be used as a casting routine to cast:
  • from one structured UDT to another structured UDT.
  • from a structured UDT to a predefined data type.
  • from a predefined data type to a structured UDT.
Yes

A pair of implicit casts is necessary to achieve full functionality for a distinct UDT: one that casts from the UDT to its fromsql transform target type and one that casts from its tosql transform source type to the UDT. Any additional cast definitions are optional.

Declaration of the external casting routine referenced by CREATE CAST.
  • CREATE FUNCTION

    or

  • CREATE METHOD
Declares the external casting routine referenced in the CREATE CAST statement. No