SQL UDF Usage | CREATE/REPLACE FUNCTION | VantageCloud Lake - Using Functions with CREATE/REPLACE FUNCTION (SQL Form) - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Function Identifiers in CREATE FUNCTION and REPLACE FUNCTION (SQL Form)

SQL UDF function names (see the following section in this topic) are distinct from SQL UDF specific function names.

The following table briefly outlines the differences among the different function identifiers:

Function Identifier Syntax Variable Name Definition
Function name function_name The identifier used to call the function from an SQL request.
  • If a function has a specific name, function_name is not the name by which the dictionary knows the function as a database object, and it need not be unique within its containing database or user.
  • If no specific name is assigned to the function, the function name is the data dictionary is function_name.
Specific function name specific_function_name The identifier used to define the function as a database object in the dictionary table DBC.TVM.

Function Name in CREATE FUNCTION and REPLACE FUNCTION (SQL Form)

Use the function name to call the SQL function from an SQL request. The function name is not necessarily the database object name that is stored in DBC.TVM.

If a specific name is not assigned to the function, the name stored in DBC.TVM is its function name, which must be unique within its database.

If a specific name is assigned to the function, the name stored in DBC.TVM is its specific function name, which need not be unique within its database.

You can give an SQL function the same name as a column, but avoid ambiguity. For example, suppose text_find is the name of an SQL function and is also the name of a column in the sales table. If the column name is followed with a database-style data type specification as in the following example, then the system assumes that text_find is a reference to the function named text_find, and not a reference to the identically named column text_find.

text_find(FLOAT),
There are two ways to make your request unambiguous.
  • Use the ANSI syntax for CAST to make an explicit declaration of data type rather than a function parameter. For example:
    CAST (text_find as FLOAT)
  • Qualify the column name fully. For example:
    sales.text_find (FLOAT),

    In this example, sales is the table that contains the column named text_find.

You can precede the SQL function name with its containing database or user name if the function is not created in your default database or user. The scope of the name is the database or user in which it is contained.

Multiple functions can have the same function name. This is called function name overloading. If you overload function names, the parameter type specifications among the overloaded function names must be sufficiently different to be distinguishable. See the last section in this topic for a list of the rules the system uses to determine the uniqueness of a function by its parameters.

Function Calling Argument in CREATE FUNCTION and REPLACE FUNCTION (SQL Form)

The function calling argument is any simple SQL expression, including, but not limited to, constant values, column references, host variables, or an expression containing any of these, including expressions containing UDFs.

When you call an SQL function, and that function is not stored in either your default database or in database SYSLIB, you must fully qualify the function call with a database name. If your default database and SYSLIB both contain functions matching the name of the called function, then the system references the UDF in your default database. This is the only exception that requires an explicit qualification for SYSLIB.

The argument types passed in the call must be compatible with the parameter declarations in the function definition of an existing function. If multiple functions have the same name, and no qualifying database is specified, then the function that is picked is determined by the following process:
  1. Vantage searches the list of built-in functions.

    If the called function has the same name as a built-in function, the search stops and that function is used.

    If a candidate function is not found, proceed to stage 2.

  2. Vantage searches the list of SQL and external function names in the default user database.

    Candidate functions are those having the same name and number of parameters as specified by the function call and the best fit based on their parameter type.

    If a candidate function is not found, proceed to stage 3.

  3. Vantage searches the list of function names in the SYSLIB database.

    Candidate functions are those having the same name and number of parameters as specified by the function call and the best fit based on their parameter type.

    If a candidate function cannot be located, the system returns an error to the requestor.

The rules for invoking an SQL UDF using an SQL expression are as follows:
  • An SQL expression passed as an argument must not be a Boolean value expression, that is, a conditional expression.

    This is also true for external UDFs.

  • Nondeterministic SQL expressions, meaning expressions that use either random functions or nondeterministic UDFs, or both, that are passed as an argument must not correspond to a parameter that is used more than once in the RETURN clause of an SQL UDF.
  • An SQL expression passed as an argument cannot be a scalar subquery.

The rules for selecting a best fit candidate user-defined function once its containing database has been determined are described in the following section.

Function Name Overloading

Function names need not be unique within a function class. However, functions from two different function classes cannot have the same name within the same database or user. Vantage uses the parameter types of identically named functions to distinguish among them, so parameter types associated with overloaded function names must be sufficiently different to be distinct.

Vantage uses the precedence order for compatible type parameters to determine which function to invoke when multiple functions with the same name must be differentiated by their parameter types.

Vantage follows a set of parameter rules to determine the uniqueness of a function name. These rules are provided in the following list:
  • The following numeric parameter types listed in order of precedence for determining function uniqueness. For example, a BYTEINT fits into a SMALLINT and a SMALLINT fits into an INTEGER. Conversely, a FLOAT does not fit into an INTEGER without a possible loss of information.
    The types are distinct and compatible. Types sharing a number are synonyms and are not distinct.
    • BYTEINT
    • SMALLINT
    • INTEGER
    • DECIMAL
NUMERIC

      The size specification for DECIMAL and NUMERIC types does not affect the distinctiveness of a function. For example, DECIMAL(8,3) and DECIMAL(6,2) are identical with respect to determining function uniqueness.

    • FLOAT, DOUBLE PRECISION, REAL
  • The following character parameter types are listed in order of precedence for determining function uniqueness.
    The types are distinct and compatible. Types sharing a character are synonyms and are not distinct. The length specification of a character string does not affect the distinctiveness of a function. For example, CHARACTER(10) and CHARACTER(5) are identical with respect to determining function uniqueness. CHARACTER SET clauses also have no effect on the determination of function uniqueness.
    • CHARACTER
    • VARCHAR, CHARACTERVARYING, LONGVARCHAR
    • CHARACTER LARGE OBJECT
  • The following graphic parameter types are distinct and compatible. Types sharing a bullet are synonyms and are not distinct.
    • GRAPHIC
    • VARGRAPHIC

      LONG VARCHAR CHARACTER SET GRAPHIC

  • The following byte parameter types are distinct and compatible:
    • BYTE
    • VARBYTE
    • BINARY LARGE OBJECT
  • All date, time, timestamp, and interval parameter types are distinct.
  • If the number of parameters in identically named existing functions is different or if the function parameter types are distinct in at least one parameter, then the function being defined is considered to be unique.
  • If more than one function has the same function_name, then you must supply a specific_function_name.
  • You can only overload function names within the same class within a given database. For example, you cannot have a scalar function and an aggregate function with the same function_name within the same database.