15.00 - Function of Casting UDTs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Function of Casting UDTs

A cast operation converts the specified source_data_type to the specified target_data_type using the specified cast_function or cast_method routine analogous to the CAST operator used to convert predefined data types (see SQL Functions, Operators, Expressions, and Predicates for more information about the CAST operator). At least one of source_data_type or target_data_type must be a UDT (see “CREATE TYPE (Distinct Form)” on page 746 and “CREATE TYPE (Structured Form)” on page 758).

Casts are the mechanism the system uses to make conversions in either direction between a UDT and another data type, which might or might not be another UDT. UDTs can have multiple casts, where each cast supports the conversion for a particular source‑target combination.

For example, the UDT named circle_type might have the following four separate casts:

  • circle_type to VARCHAR
  • VARCHAR to circle_type
  • circle_type to VARBYTE
  • VARBYTE to circle_type
  • Once you have created a user‑defined cast, it is immediately available for use with the SQL CAST function (see SQL Functions, Operators, Expressions, and Predicates). For example, if you have defined a cast to convert a circle UDT to a VARCHAR(40), then you can specify the following cast operation:

        CAST(circle_udt_expression AS VARCHAR(40))

    The system automatically and implicitly supports casts from or to a predefined data type when you cast a compatible predefined type to the appropriate predefined type serving as the cast source of target type.

    The scope of operations under which the system applies UDT-related implicit casts is a subset of the scope of operations under which traditional predefined data type‑related implicit casts are applied (see SQL Functions, Operators, Expressions, and Predicates).

    For example, consider the following equality comparison in a WHERE clause:

        WHERE myUdt = NEW CircleUdt(1,1,9)

    This is not within the scope of the implicit casts the system supports because it is not an assignment operation, supported system operator, or supported predefined function, so no implicit casts is applied. In such cases, you must specify explicit cast operations to ensure that the UDTs being compared are identical.