Avoiding Name Clashes Among UDFs, UDMs, UDTs | Teradata Vantage - 17.10 - Naming Conventions: Avoiding Name Clashes Among UDFs, UDMs, and UDTs - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
For UDF, UDM, and UDT names:
  • The DatabaseID, TVMName column pair must be unique within the DBC.TVM table. Use the DBC.Tables2 view to view rows from DBC.TVM.
  • The signature of the database_name.routine_name(parameter_list) routine must be unique.

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

The Database ID column entry is always the same. The name uniqueness is dependent on the TVMName column value only.

TVMName Entry for UDTs and UDMs

The following describes the TVMName entry for UDTs and UDMs.

UDTs created by a CREATE TYPE request have a SPECIFIC name that is system-generated based on the specified UDT name. For information on object names, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

The database generates the SPECIFIC name by truncating any UDT names that exceed the permitted number of characters.

When you submit a CREATE TYPE statement, the system automatically generates a corresponding UDF to obtain a UDT instance.

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

Type of External Routine SPECIFIC Name
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.

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.

For a 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.

The names of transform groups are stored in DBC.UDTTransform.

The system adds SPECIFIC method names to the TVMName column in DBC.TVM for:
  • Observer and mutator methods, which are auto-generated for structured type attributes.
  • Instance methods and constructor methods created by CREATE TYPE, ALTER TYPE, or CREATE METHOD statements where the coder does not specify a SPECIFIC method name.

A SPECIFIC name of up to 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:

  1. The first 8 characters of the UDT name.
  2. A LOW LINE (underscore) character.
  3. The first 10 characters of the for observer attribute name, mutator attribute name, instance method name, or constructor method name, as appropriate.
  4. A LOW LINE (underscore) character.
  5. The last 8 HEXADECIMAL digits of the routine identifier assigned to the observer, mutator, instance, or constructor method name, as appropriate.
  6. A character sequence is appended, _O for observer, _M for mutator, _R for instance, or _C for constructor, appropriate.

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