15.00 - Function of Methods - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Function of Methods

A user-defined method, also referred to as a method or UDM, is a special kind of UDF that has the following properties:

  • It is uniquely associated with a particular UDT.
  • It is always created in the SYSUDTLIB database.
  • It is invoked using dot notation, which is in the form of udt_expression.method(…) (see SQL Functions, Operators, Expressions, and Predicates for details).
  • It can be written in either C or C++ (see SQL External Routine Programming for details).
  • Methods must be defined both as part of the UDT definition as specified by its CREATE TYPE statement (see “Method Definitions Are Split Between CREATE METHOD And CREATE TYPE Definitions” on page 403, “CREATE TYPE (Distinct Form)” on page 746 and “CREATE TYPE (Structured Form)” on page 758) and as a separate method body definition using CREATE METHOD. The definition made by a CREATE METHOD statement actually declares the method, including its EXTERNAL NAME and EXTERNAL SECURITY definitions, if required.

    You can add the signatures for newly defined methods to a type definition or drop the signature for an existing method using the ALTER TYPE statement (see “ALTER TYPE” on page 148).

    Each method has an associated signature, defined as its method name and its declared parameter list. The method signature must match one of the method signatures that was specified in the CREATE TYPE statement of the specified UDT (see “CREATE TYPE (Distinct Form)” on page 746 and “CREATE TYPE (Structured Form)” on page 758), or in a type redefinition made by an ALTER TYPE statement (see “ALTER TYPE” on page 148).

    In other words, there must be a method signature associated with the UDT such that the following statements are all true:

  • Its method name is the same.
  • Its parameter list is the same.
  • If the method is defined as a CONSTRUCTOR type, then the method signature also includes the CONSTRUCTOR keyword and its associated UDT must be a structured type.
  • The declared parameter list is that specified in the method specification of its associated CREATE TYPE statement. The system generates an augmented parameter list for each method internally. The augmented parameter list has the associated UDT name, or subject parameter, as its first parameter and the declared parameter list as its second parameter. Together, these form the augmented parameter list.

    For example, consider the following method specifications for the UDT named udt_name:

        CREATE TYPE udt_name
         CONSTRUCTOR METHOD udt_name(CP1, CP2, …) …,
         INSTANCE METHOD M1(MP1, MP2, …) …,
         … ;

    The following table describes the declared and augmented parameter lists for these methods:



    Declared Parameter Lists

    Augmented Parameter Lists


    (CP1, CP2, …)

    (udt_name, CP1, CP2, …)


    (MP1, MP2, …)

    (udt_name, MP1, MP2, …)

    The augmented parameter list is used to check for conflicts between methods specified in CREATE TYPE or ALTER TYPE statements and existing methods and UDFs. The augmented parameter list is also used in the method resolution process.

    You can declare any data type, including LOBs, UDTs, TD_ANYTYPE, and VARIANT_TYPE for a method parameter.

    Note: You can only declare an input parameter to have the VARIANT_TYPE data type, but you can declare any method parameter to have the TD_ANYTYPE data type. For more information on developing methods, see SQL External Routine Programming.

    Methods support both name overloading and overloading of server character sets or precision. Multiple methods of a UDT can have the same method name as long as their parameter lists are different. Overloading of character sets and precision is supported by using the TD_ANYTYPE data type for parameters and return types.

    By defining a method using TD_ANYTYPE as a parameter data type, you can overload the method based on its server character set or numeric precision rather than its name. When you define a method using TD_ANYTYPE, Teradata Database determines the parameter data types at execution time based on the parameters that are provided.

    In addition to allowing TD_ANYTYPE to act as an alias for the type for parameter data types with undetermined attributes, you can also use TD_ANYTYPE to resolve all possible parameter data types. This allows you to develop fewer signatures for the same basic method as well as to provide flexibility in coding the logic for the required method behavior.

    However, using TD_ANYTYPE as a parameter data type results in the loss of the implicit conversions that automatically convert input values to those that match the method signature. As a result, you have a greater responsibility to make sure that any undefined parameters are properly resolved and processed.