Represents a custom data type for modeling the structure and behavior of real-world entities used by applications.
- The name of the database in which all UDTs are created.
- The name of a UDT that was created with a CREATE TYPE statement.
- Appropriate data type attributes.
- A UDT column supports the following attributes:
- NOT NULL
- DEFAULT NULL
- For details on using data type attributes with UDTs, see:
- A UDT column does not support column storage or column constraint attributes.
UDTs are ANSI SQL:2011 compliant.
Types of UDTs
Teradata Database supports distinct and structured UDTs.
|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 (nesting is supported).||A structured UDT named circle can consist of x-coordinate, y-coordinate, and radius attributes.|
UDTs can further be classified as LOB UDTs or nonLOB 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 UDT.|
|the predefined data type on which it is based is not a LOB||nonLOB UDT.|
|structured||the data type of at least one attribute is CLOB, BLOB, or LOB UDT||LOB UDT.|
|none of the attributes has a LOB or LOB UDT data type||nonLOB 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 default 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 External Representations for UDTs.
Support for Multiple Transform Groups
You can create multiple transform groups for each UDT by using the CREATE TRANSFORM or REPLACE TRANSFORM statements. CREATE TRANSFORM also allows you to add transform groups for a UDT that already has existing transform groups. REPLACE TRANSFORM drops all existing transform groups for a UDT and creates new transform groups for the UDT. You can also use DROP TRANSFORM to delete transform groups from a UDT.
The maximum number of transform groups allowed for a particular UDT is 16.
When using the CREATE TRANSFORM statement, it is possible to only specify either a from-sql or to-sql function for a transform. However, in order to create a table with a UDT column, the default transform group for the UDT must contain both from-sql and to-sql functions in the transform group.
You can use the TRANSFORM option in the CREATE PROFILE/MODIFY PROFILE or CREATE USER/MODIFY USER statements to specify for a user the particular transform group that will be used for a given data type.
You can use the following macros to find the transform group for a UDT (or CDT), or the transform group settings for a user or profile.
|SYSUDTLIB.HelpCurrentUserTransforms||Lists the transform group settings of the current logon user.|
|SYSUDTLIB.HelpUserTransforms(User)||Lists the transform group settings for a specific user.|
|SYSUDTLIB.HelpCurrentUDTTransform(UDT)||Lists the current transform group for a UDT.|
|SYSUDTLIB.HelpUDTTransform(User,UDT)||Lists the transform group for a UDT for a user.|
|SYSUDTLIB.HelpProfileTransforms(Profile)||Lists the transform group settings for a specific profile.|
|Lists the transform group for a UDT for a profile.|
For more information about these macros, see SQL Data Definition Language - Syntax and Examples.
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
- Restrictions that apply to CLOB and BLOB data types also apply to LOB UDTs:
- A table can have a maximum combination of 32 CLOB, BLOB, or LOB UDT columns.
- Queue tables cannot have CLOB, BLOB, or LOB UDT columns.
- If you have existing UDTs with non-ASCII characters in the name, 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.
Functions That Operate on UDTs
You can specify UDTs as parameters and return types for UDFs written in C, C++, or Java. This includes scalar and aggregate UDFs, table functions, and table operators.
You can specify UDTs as IN, INOUT, and OUT parameters of stored procedures and external stored procedures written in C, C++, or Java.
FNC functions and Java classes and methods are provided to enable a UDF or external stored procedure to access and set the value of a UDT parameter, or to get information about the UDT parameter. For information about these functions and methods, see SQL External Routine Programming.
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.
Archiving and Restoring UDTs
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.
Example: UDT Data Type
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);
Example: Distinct UDT Parameter in a Java UDF
CREATE TYPE MONEY AS numeric(10,2) FINAL REPLACE FUNCTION MyMoney (A1 MONEY) RETURNS MONEY LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mymoney'; public static java.math.BigDecimal mymoney(java.math.BigDecimal a) throws SQLException
Alternatively, you can define the function as follows:
REPLACE FUNCTION MyMoney (A1 MONEY) RETURNS MONEY LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mymoney(java.math.BigDecimal) returns java.math.BigDecimal'; public static java.math.BigDecimal mymoney(java.math.BigDecimal a) throws SQLException
Example: Structured UDT Parameter in a Java UDF
CREATE TYPE CIRCLE AS (x double, y double, r double)… REPLACE FUNCTION MyCircle(A1 CIRCLE) RETURNS INTEGER LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mycircle'; public static int mycircle(java.sql.Struct s) throws SQLException
Alternatively, you can define the function as follows:
REPLACE FUNCTION MyCircle(A1 CIRCLE) RETURNS INTEGER LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mycircle(java.sql.Struct) returns int'; public static int mycircle(java.sql.Struct s) throws SQLException
Example: Distinct UDT Parameter in a Java External Stored Procedure
CREATE TYPE MONEY AS numeric(10,2) FINAL REPLACE PROCEDURE MyMoney(IN A1 MONEY, OUT A2 MONEY) LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mymoney'; public static void MyMoney(java.lang.BigDecimal A1, java.lang.BigDecimal A2) throws SQLException
Example: Structured UDT Parameter in a Java External Stored Procedure
CREATE TYPE CIRCLE AS (x double, y double, r double) REPLACE PROCEDURE MyCircle(IN A1 CIRCLE, OUT A2 INTEGER) LANGUAGE JAVA NO SQL PARAMETER STYLE JAVA EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mycircle'; public static void(java.sql.Struct A1, int A2) throws SQLException
|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 and dropping transform definitions||
|specifying nondefault transform group settings for a user||“CREATE/MODIFY PROFILE” and “CREATE/MODIFY USER” 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.|