Enabling Full Functionality For a Structured UDT | Teradata Vantage - Enabling Full Functionality For a Structured UDT - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

When you create a structured UDT, the system automatically generates a constructor function in the SYSUDTLIB database for each structured type. The constructor function returns a structured type instance whose attributes are set to their default values. The default value for any attribute of a structured UDT is null.

Because the system generates this functionality automatically by means of interaction with dictionary tables, and not by submitting a CREATE FUNCTION request, it is not possible to submit a SHOW FUNCTION request to report the create text DDL for the system-generated constructor.

Unlike the case for distinct UDTs, the system does not automatically generate casting, ordering, or transform functionalities for structured UDTs. See Default Capabilities of Structured UDTs, Default Cast For a Structured UDT, Default Ordering For a Structured UDT, and Default Transform Group For a Structured UDT. Instead, you must perform all of the statements listed as Yes under the Mandatory? column in the following table to enable the full functionality of a newly created structured UDT.

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 a constructor method. CREATE METHOD or CREATE FUNCTION Declares any SQL-invoked constructor methods to be used with the structured UDT.

Teradata Database generates a constructor function for a newly created structured UDT automatically. See System-Generated Constructor Functions.

No
Definition of an instance method. CREATE METHOD and CREATE TYPE (Structured Form) Declares any SQL-invoked instance methods to be associated with the structured UDT.

Teradata Database generates an observer and a mutator instance method for each attribute of a newly created structured UDT automatically. See System-Generated Observer and Mutator Methods.

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.

Although it is not mandatory to create casting functionality to be able to use a structured UDT as the data type for a table column, you cannot perform some operations on that column unless a casting has been defined for the type. See Default Cast For a Structured UDT.

The minimum requirement to achieve full functionality is the two implicit casts that cast from a UDT to a fromsql transform target predefined data type and from the tosql transform source data type to the UDT.

Yes
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

If you do not create all the mandatory functionalities listed in this table, the structured UDT you create does not have full functionality. Furthermore, you cannot perform any unsupported operations on values having the data type. Attempts to do so return an error to the requestor.

If your applications of the current structured UDT require additional functionality, you can write instance or constructor methods in either the C or C++ language, and then submit CREATE METHOD requests to declare any SQL-invoked methods to be associated with their structured UDT. For details about writing multiple constructor methods for a structured UDT, see Constructor Methods.