UDM Types | SQL External Routine Programming | Teradata Vantage - 17.10 - UDM Types - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)
Teradata supports two types of UDMs:
  • Instance
  • Constructor

Instance Methods

An instance method operates on a specific instance of a distinct or structured UDT. For example, an instance method named area might calculate and return the area of a structured UDT named circle that contains attributes x, y, and radius.

You can replace a column name in an SQL expression with an instance method invocation on a UDT. When Vantage evaluates the expression, it invokes the UDM.

Instance methods can also provide transform, ordering, and cast functionality for a distinct or structured UDT. You do not invoke these types of instance methods directly. Vantage uses this functionality during certain operations involving the UDT.

WHEN you … THEN Vantage uses this functionality …
convert the UDT to a predefined data type or another UDT, for example with the CAST function cast
convert another UDT or predefined data type to the UDT
compare two UDTs, for example with the ORDER BY clause ordering
export the UDT from the server transform

Vantage also uses transform functionality to import the UDT to the server, for example with the USING modifier. However, the implementation is always a UDF, not a UDM.

Before you can create a table that has a UDT column and perform queries on the column, the UDT must have UDFs or instance methods associated with it that provide transform, ordering, and cast functionality.

IF the UDT is … THEN …
distinct Vantage automatically generates UDFs and methods that provide transform, ordering, and cast functionality.
structured you are responsible for implementing UDFs or UDMs that provide cast, ordering, and transform functionality.

Constructor Methods

A constructor method initializes an instance of a structured UDT.

A structured UDT can have more than one constructor method, each one providing different initialization options.

To create an instance of a structured UDT in an SQL statement, you can invoke the constructor method or specify the constructor method in a NEW expression.