15.00 - User-Defined Types - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

User-Defined Types

SQL provides a set of predefined data types, such as INTEGER and VARCHAR, that you can use to store the data that your application uses, but they might not satisfy all of the requirements you have to model your data.

User-defined types (UDTs) allow you to extend SQL by creating your own data types and then using them like predefined data types.

UDT Types

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 UDT that is a collection of one or more fields called attributes, each of which 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.

Distinct and structured UDTs can define methods that operate on the UDT. For example, a distinct UDT named euro can define a method that converts the value to a US dollar amount. Similarly, a structured UDT named circle can define a method that computes the area of the circle using the radius attribute.

Teradata Database also supports a form of structured UDT called dynamic UDT. Instead of using a CREATE TYPE statement to define the UDT, like you use to define a distinct or structured type, you use the NEW VARIANT_TYPE expression to construct an instance of a dynamic UDT and define the attributes of the UDT at run time.

Unlike distinct and structured UDTs, which can appear almost anywhere that you can specify predefined types, you can only specify a dynamic UDT as the data type of (up to eight) input parameters to external UDFs. The benefit of dynamic UDTs is that they significantly increase the number of input arguments that you can pass in to external UDFs.

Using a Distinct UDT

Here is a synopsis of the steps you take to develop and use a distinct UDT:

1 Use the CREATE TYPE statement to create a distinct UDT that is based on a predefined data type, such as INTEGER or VARCHAR.

Teradata Database automatically generates functionality for the UDT that allows you to import and export the UDT between the client and server, use the UDT in a table, perform comparison operations between two UDTs, and perform data type conversions between the UDT and the predefined data type on which the definition is based.

2 If the UDT defines methods, write, test, and debug the C or C++ code for the methods, and then use CREATE METHOD or REPLACE METHOD to identify the location of the source code and install it on the server.

The methods are compiled, linked to the dynamic linked library (DLL or SO) associated with the SYSUDTLIB database, and distributed to all Teradata Database nodes in the system.

3 Use GRANT to grant privileges to users who are authorized to use the UDT.

4 Use the UDT as the data type of a column in a table definition.

Using a Structured UDT

Here is a synopsis of the steps you take to develop and use a structured UDT (that is not a dynamic UDT):

1 Use the CREATE TYPE statement to create a structured UDT and specify attributes, constructor methods, and instance methods.

Teradata Database automatically generates the following functionality:

  • A default constructor function that you can use to construct a new instance of the structured UDT and initialize the attributes to NULL
  • Observer methods for each attribute that you can use to get the attribute values
  • Mutator methods for each attribute that you can use to set the attribute values
  • 2 Follow these steps to implement, install, and register cast functionality for the UDT (Teradata Database does not automatically generate cast functionality for structured UDTs):

    a Write, test, and debug C or C++ code that implements cast functionality that allows you to perform data type conversions between the UDT and other data types, including other UDTs.

    b Identify the location of the source code and install it on the server:

     

    IF you write the source code as a …

    THEN use one of the following statements …

    method

    CREATE METHOD or REPLACE METHOD

    function

    CREATE FUNCTION or REPLACE FUNCTION

    The source code is compiled, linked to the dynamic linked library (DLL or SO) associated with the SYSUDTLIB database, and distributed to all Teradata Database nodes in the system.

    c Use the CREATE CAST or REPLACE CAST statement to register the method or function as a cast routine for the UDT.

    d Repeat Steps a through c for all methods or functions that provide cast functionality.

    3 Follow these steps to implement, install, and register ordering functionality for the UDT (Teradata Database does not automatically generate ordering functionality for structured UDTs):

    a Write, test, and debug C or C++ code that implements ordering functionality that allows you to perform comparison operations between two UDTs.

    b Identify the location of the source code and install it on the server:

     

    IF you write the source code as a …

    THEN use one of the following statements …

    method

    CREATE METHOD or REPLACE METHOD

    function

    CREATE FUNCTION or REPLACE FUNCTION

    The source code is compiled, linked to the dynamic linked library (DLL or SO) associated with the SYSUDTLIB database, and distributed to all Teradata Database nodes in the system.

    c Use the CREATE ORDERING or REPLACE ORDERING statement to register the method or function as an ordering routine for the UDT.

    4 Follow these steps to implement, install, and register transform functionality for the UDT (Teradata Database does not automatically generate transform functionality for structured UDTs):

    a Write, test, and debug C or C++ code that implements transform functionality that allows you to import and export the UDT between the client and server.

    b Identify the location of the source code and install it on the server:

     

    IF the source code implements transform functionality for …

    THEN …

    importing the UDT to the server

    you must write the source code as a C or C++ UDF and use CREATE FUNCTION or REPLACE FUNCTION to identify the location of the source code and install it on the server.

    exporting the UDT from the server

     

     

     

     

     

     

     

     

     

    IF you write the source code as a …

    THEN use one of the following statements to identify the location of the source code and install it on the server …

     

     

    method

    CREATE METHOD or REPLACE METHOD

     

     

    function

    CREATE FUNCTION or REPLACE FUNCTION

     

     

     

     

     

    The source code is compiled, linked to the dynamic linked library (DLL or SO) associated with the SYSUDTLIB database, and distributed to all Teradata Database nodes in the system.

    c Repeat Steps a through b.

     

    IF you took Steps a through b to implement and install this transform functionality …

    THEN repeat Steps a through b to implement and install this transform functionality …

    importing the UDT to the server

    exporting the UDT from the server

    exporting the UDT from the server

    importing the UDT to the server

    d Use the CREATE TRANSFORM or REPLACE TRANSFORM statement to register the transform routines for the UDT.

    5 If the UDT defines constructor methods or instance methods, write, test, and debug the C or C++ code for the methods, and then use CREATE METHOD or REPLACE METHOD to identify the location of the source code and install it on the server.

    The methods are compiled, linked to the dynamic linked library (DLL or SO) associated with the SYSUDTLIB database, and distributed to all Teradata Database nodes in the system.

    6 Use GRANT to grant privileges to users who are authorized to use the UDT.

    7 Use the UDT as the data type of a column in a table definition.

    Using a Dynamic UDT

    Follow these steps to use a dynamic UDT as the data type of an input parameter to an external UDF:

    1 In the CREATE FUNCTION or REPLACE FUNCTION statement for the UDF, specify the data type of up to eight input parameters as VARIANT_TYPE.

    2 Write, test, and debug the C or C++ source code for the UDF.

    3 Call the UDF, using the NEW VARIANT_TYPE expression to construct instances of dynamic UDT arguments and define up to 128 attributes for each UDT.

    UDT Indexing

    A user can declare a primary or secondary index on a UDT column when issuing a CREATE TABLE, CREATE INDEX, CREATE JOIN INDEX or CREATE HASH INDEX statement.

    Once the index has been created, the user can issue DML statements containing UDT predicate and/or UDT join term expressions.

    Related Topics

     

    For more information on …

    See …

  • CREATE TYPE
  • CREATE METHOD and REPLACE METHOD
  • CREATE FUNCTION and REPLACE FUNCTION
  • CREATE CAST and REPLACE CAST
  • CREATE ORDERING and REPLACE ORDERING
  • CREATE TRANSFORM and REPLACE TRANSFORM
  • SQL Data Definition Language.

    UDT expressions, including NEW and NEW VARIANT_TYPE

    SQL Functions, Operators, Expressions, and Predicates.

    writing, testing, and debugging source code for a constructor method or instance method

    SQL External Routine Programming.

     

    writing, testing, and debugging source code for a UDF that uses UDT types

    archiving and restoring UDTs and methods

    Teradata Archive/Recovery Utility Reference.