17.10 - Using a Structured UDT - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)
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.
    Vantage 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 (Vantage does not automatically generate cast functionality for structured UDTs):
    1. 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.
    2. 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 database nodes in the system.

    3. Use the CREATE CAST or REPLACE CAST statement to register the method or function as a cast routine for the UDT.
    4. 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 (Vantage does not automatically generate ordering functionality for structured UDTs):
    1. Write, test, and debug C or C++ code that implements ordering functionality that allows you to perform comparison operations between two UDTs.
    2. 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 database nodes in the system.

    3. 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 (Vantage does not automatically generate transform functionality for structured UDTs):
    1. 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.
    2. 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 to 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 database nodes in the system.

    3. 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
    4. 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 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.