15.00 - Naming Conventions: Avoiding Name Clashes Among UDFs, UDMs, and UDTs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Naming Conventions: Avoiding Name Clashes Among UDFs, UDMs, and UDTs

For UDF, UDM, and UDT names not to clash, they must observe the following two rules:

  • The following column pair must be unique within the DBC.TVM table (use the DBC.Tables2 view to view rows from DBC.TVM).
  • DatabaseID, TVMName
  • The signature of the following routine must be unique:
  • database_name.routine_name(parameter_list).
  • UDFs, UDMs, and UDTs can have the same SQL names as long as their SPECIFIC names and associated routine signatures are different. In the case of UDTs, the SPECIFIC name reference is to the SPECIFIC names of any method signatures within a UDT definition, not to the UDT itself, which does not have a SPECIFIC name.

    The value of database_name is always SYSUDTLIB for UDFs associated with UDTs, including UDFs used to implement the following functionality on behalf of a UDT:

  • Casts
  • Orderings
  • Transforms
  • This means that the Database ID column entry is always the same. Because of this, name uniqueness is dependent on the TVMName column value only.

    The following list highlights the algorithm used to determine the TVMName entry for UDTs and UDMs.

  • UDTs created by a CREATE TYPE request have a SPECIFIC name that follows the rules for object names documented in SQL Fundamentals. This name is system‑generated based on the specified UDT name.
  • Teradata Database generates the SPECIFIC name by truncating any UDT names that exceed the permitted number of characters (see SQL Fundamentals).

  • Every time you submit a CREATE TYPE request, the system automatically generates a UDF on its behalf to obtain a UDT instance.
  • Observer methods, auto-generated on behalf of structured type attributes, have a TVMName column entry constructed as shown by the following process:
  • a A SPECIFIC name of at most 28 characters is generated based on the UDT and attribute names.

    The SPECIFIC name is generated as the concatenation of the following elements in the order indicated:

    i The first 8 characters of the UDT name.

    i The first 8 characters of the UDT name.

    ii A LOW LINE (underscore) character.

    iii The first 10 characters of the attribute name.

    iv A LOW LINE (underscore) character.

    v The last 8 HEXADECIMAL digits of the routine identifier assigned to the observer instance method.

    b The character sequence _O is appended after the characters generated by Stage a.

    The remaining characters, up to the 30th character, are filled with SPACE characters.

    c End of process.

  • Mutator methods, auto-generated on behalf of structured type attributes, have a TVMName entry constructed as shown by the following process:
  • a A SPECIFIC name of at most 28 characters is generated based on the UDT and attribute names.

    The SPECIFIC name is generated as the concatenation of the following elements in the order indicated.

    i The first 8 characters of the UDT name.

    i The first 8 characters of the UDT name.

    ii A LOW LINE (underscore) character.

    iii The first 10 characters of the attribute name.

    iv A LOW LINE (underscore) character.

    v The last 8 HEXADECIMAL digits of the routine identifier assigned to the mutator instance method.

    b The character sequence _M is appended after the characters generated from Stage a.

    The remaining positions in the SPECIFIC name are filled with SPACE characters.

    c End of process.

  • The system adds the SPECIFIC method names of instance methods created by a CREATE TYPE, ALTER TYPE, or CREATE METHOD statement to the TVMName column in DBC.TVM.
  • If the coder of a method does not specify a SPECIFIC method name, then the system generates one for it with the following process:

    a A SPECIFIC name of at most 28 characters is generated based on the UDT and instance method names.

    The SPECIFIC name is generated as the concatenation of the following elements in the order indicated:

    i The first 8 characters of the UDT name.

    i The first 8 characters of the UDT name.

    ii A LOW LINE (underscore) character.

    iii The first 10 characters of the instance method name.

    iv A LOW LINE (underscore) character.

    v The last 8 HEXADECIMAL digits of the routine identifier assigned to the instance method.

    b The character sequence _R is appended after the characters generated from Stage a. The remaining positions in the SPECIFIC name are filled with SPACE characters.

    c End of process.

  • Teradata Database adds the SPECIFIC method names of constructor methods created by a CREATE TYPE, ALTER TYPE, or CREATE METHOD statement to the TVMName column in DBC.TVM.
  • If the coder of a method does not specify a SPECIFIC method name, then the system generates one for it with the following process:

    a A SPECIFIC name of at most 28 characters is generated based on the UDT and constructor method names.

    The SPECIFIC name is generated as the concatenation of the following elements in the order indicated:

    i The first 8 characters of the UDT name.

    i The first 8 characters of the UDT name.

    ii A LOW LINE (underscore) character.

    iii The first 10 characters of the constructor method name.

    iv A LOW LINE (underscore) character.

    v The last 8 HEXADECIMAL digits of the routine identifier assigned to the constructor method.

    b The character sequence _C is appended after the characters generated from Stage a.

    The remaining positions in the SPECIFIC name are filled with SPACE characters.

    c End of process.

    The SPECIFIC name of a UDT, UDM or UDF must be unique to avoid name clashes.

     

    FOR this type of external routine …

    The SPECIFIC name is …

    UDT

    always its UDT name.

  • UDM
  • UDF
  • user‑defined.

    There are two exceptions to this rule for structured types:

  • System‑generated observer methods.
  • System‑generated mutator methods.
  • The SPECIFIC names for these are always the same as the names of the structured UDT attributes on which they operate.

    Note that for UDMs, the SPECIFIC name is defined in its signature within its associated UDT, not within the CREATE METHOD statement.

    Similarly, the signatures of external routines must be unique. The following rules apply:

  • For every UDT you create, the system generates a UDF with the following signature: SYSUDTLIB.UDT_Name().
  • No other UDF can have this signature.

  • When you create a UDM, the system treats it as a UDF for which the data type of its first argument is the same as the UDT on which that UDM is defined.
  • For example, a UDM named area(), defined on the UDT named circle, would have the signature SYSUDTLIB.area(circle). It follows that there can be no other UDF with this same signature.

    Given the single database (SYSUDTLIB) Teradata UDT environment, the following rules apply:

  • A UDT and a SYSUDTLIB UDF with no parameters cannot have the same name.
  • A method for a structured UDT cannot have the same name as any of the attributes for that type if the signature of the method and the signature of either the observer or mutator methods for the attribute match.
  • You must define a transform group for each UDT you create. Because the system creates a transform group for you automatically when you create a distinct UDT, you cannot create an additional explicit transform group without first dropping the system‑generated transform. The names of UDT transform groups need not be unique, so you can use the same name for all transform groups if you wanted to.

    Unlike the names for UDTs, UDMs, and UDFs, the names of transform groups are stored in DBC.UDTTransform rather than DBC.TVM.