15.10 - UDT Data Type - Teradata Database

Represents a custom data type for modeling the structure and behavior of real-world entities used by applications.

where:

Syntax element …	Specifies …
SYSUDTLIB.	the name of the database in which all UDTs are created.
udt_name	the name of a UDT that was created with a CREATE TYPE statement.
attribute	appropriate data type attributes. A UDT column supports the following attributes:
	NULL NOT NULL FORMAT	TITLE NAMED DEFAULT NULL
	For details on using data type attributes with UDTs, see: Chapter 11: “Default Value Control Phrases” Chapter 12: “Data Type Formats and Format Phrases” A UDT column does not support column storage or column constraint attributes.

UDTs are ANSI SQL:2011 compliant.

Teradata Database supports distinct and structured UDTs.

UDT Type	Description	Example
Distinct	A UDT that is based on a single predefined data type, such as INTEGER or VARCHAR.	A distinct UDT named euro that is based on a DECIMAL(8,2) data type can store monetary data.
Structured	A collection of one or more fields called attributes, where each attribute is defined as a predefined data type or other UDT (which allows nesting).	A structured UDT named circle can consist of x-coordinate, y-coordinate, and radius attributes.

UDTs can further be classified as LOB-type UDTs or non-LOB-type UDTs.

IF the UDT is …	AND …	THEN the UDT is a …
distinct	the predefined data type on which it is based is a CLOB or BLOB	LOB-type UDT.
distinct	the predefined data type on which it is based is not a LOB	non-LOB-type UDT.
structured	the data type of at least one attribute is CLOB, BLOB, or LOB-type UDT	LOB-type UDT.
structured	none of the attributes has a LOB or LOB-type UDT data type	non-LOB-type UDT.

You can create user-defined methods (UDMs) that operate on distinct and structured UDTs. For example, for a distinct UDT named euro, you can define a method that converts the value to a US dollar amount. Similarly, for a structured UDT named circle, you can define a method that computes the area of the circle using the radius attribute.

Every UDT has a corresponding predefined SQL data type that Teradata Database uses for import and export operations between client applications and the server. The predefined data type that maps to a particular UDT is specified in the transform definition associated with the UDT.

IF the UDT is …

THEN …

distinct

Teradata Database automatically generates a transform definition for import and export operations that maps the UDT to the predefined data type on which it is based.

For example, consider a distinct UDT named euro that is based on a DECIMAL(8,2) data type.

The transform definition that Teradata Database automatically generates allows a client application to load data into a euro column using the external representation of DECIMAL(8,2). A client application can also perform queries on the euro column and receive values in the same format as DECIMAL(8,2).

structured

a transform definition must be created for the UDT using the CREATE TRANSFORM statement.

The transform definition specifies a predefined SQL data type that acts as a container to import and export attribute values of the structured UDT.

For example, consider a structured UDT named circle that consists of x-coordinate, y-coordinate, and radius attributes. Suppose the data type of each attribute is FLOAT.

The transform definition could map a BYTE(24) predefined type to the circle UDT, allowing eight bytes for each FLOAT attribute. The client application can load data into a circle column using the external representation of BYTE(24). Similarly, the application can perform queries on the circle column and receive the attribute values in a BYTE(24) container.

The logic for import operations that extracts the values from a predefined SQL data type and sets the corresponding UDT attributes is implemented as a UDF that is specified in the CREATE TRANSFORM statement. Similarly, the logic for export operations that packs the UDT attribute values into the predefined SQL data type is implemented as a UDF or UDM.

For details on transform definitions, see “CREATE TRANSFORM” in SQL Data Definition Language.

For more information about external representations for UDTs, see Appendix B: “External Representations for UDTs.”

To create a table that has a UDT column, you must have the UDTUSAGE, UDTTYPE, or UDTMETHOD privilege on the SYSUDTLIB database, or have the UDTUSAGE privilege on the specified UDT.

To perform a query on a UDT column, you must have the UDTUSAGE privilege on the specified UDT.

You can declare a primary or secondary index on a UDT column when you create an indexed table, join index, or hash index. However the following UDT types are not supported for any form of primary or secondary index.

LOB UDTs

VARIANT_TYPE UDT

Period, Geospatial, and ARRAY/VARRAY types.

Restrictions that apply to CLOB and BLOB data types also apply to LOB-type UDTs:

A table can have a maximum combination of 32 CLOB, BLOB, or LOB-type UDT columns.

Queue tables cannot have CLOB, BLOB, or LOB-type UDT columns.

If you have existing UDTs with non-ASCII characters in the name, then you cannot use the ASCII session character set to create new UDTs with LATIN characters in the name. You must use the UTF8 or UTF16 session character sets instead.

You can use UDTs as input arguments and return values of UDFs written in C or C++. You cannot use UDTs as input arguments and return values of UDFs written in Java.

You can also use UDTs as IN, INOUT, and OUT parameters of stored procedures and external stored procedures written in C or C++. However, you cannot use UDTs as IN, INOUT, and OUT parameters of external stored procedures written in Java.

You can use UDTs with most SQL functions and operators, with the exception of ordered analytical functions, provided that the following is true:

An implicit cast definition exists that casts the UDT to a predefined type that is accepted by the function or operator.

The DisableUDTImplCastForSysFuncOp DBS Control field is set to zero.

For details on using UDTs with SQL functions and operators, see SQL Functions, Operators, Expressions, and Predicates.

You can archive and restore UDTs only as part of a full database archival and restoration.

Individual UDTs cannot be archived or restored using the ARCHIVE (DUMP) or RESTORE statements. For information on archiving and restoring database objects, see Teradata Archive/Recovery Utility Reference.

Consider the following statement that creates a distinct UDT named euro:

   CREATE TYPE euro

   AS DECIMAL(8,2)

   FINAL;

The following statement creates a table that defines a euro column named sales:

   CREATE TABLE european_sales

     (region INTEGER

     ,sales euro);

FOR information on …	SEE …
functions and operators that support UDTs	SQL Functions, Operators, Expressions, and Predicates.
implementing the functionality for UDFs, UDMs, and external stored procedures that operate on UDTs	SQL External Routine Programming.
creating UDT definitions	“CREATE TYPE” in SQL Data Definition Language.
creating transform definitions	“CREATE TRANSFORM” in SQL Data Definition Language.
creating cast definitions	“CREATE CAST” in SQL Data Definition Language.
creating tables with UDT columns	“CREATE TABLE” in SQL Data Definition Language.
DisableUDTImplCastForSysFuncOp DBS Control field	Utilities.
archiving, copying and restoring UDTs	Teradata Archive/Recovery Utility Reference.