15.00 - Function Overloading - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Function Overloading

Most function names need not be unique within a function class. Teradata Database uses the parameter types of identically named functions to distinguish among them, so parameter types associated with overloaded function names must be distinct.

Teradata Database uses the precedence order for compatible type parameters to determine which function is to be invoked when several functions having the same name must be differentiated by their parameter types. See SQL External Routine Programming for further information about function overloading.

Teradata Database does not support name overloading for functions that implement row‑level security policies because of the parameter types required by the system for input to the function. The same number of parameters and their corresponding data types are required for each UDF that executes the security policy for a specific statement‑action.

The only difference that can exist between the parameters for UDFs that execute a security policy is that the parameters of different UDFs can include or omit null indicators, depending on whether the constraint allows nulls.

You can simplify function name overloading by using dynamic UDTs to eliminate the need to create multiple UDFs as input parameter data types to cover an overloaded name. A dynamic UDT is a structured UDT with the preassigned name VARIANT_TYPE, which dynamically constructs its attributes at runtime based on the attributes the request passes to it.

For example, suppose you create the distinct UDT integer_udt and then create a UDF using VARIANT_TYPE as the data type for its only parameter:

     CREATE TYPE integer_udt AS INTEGER FINAL;
 
     CREATE FUNCTION udf_agch002002dyn_udt 
       (parameter_1  VARIANT_TYPE)  
     RETURNS INTEGERUDT 
     CLASS AGGREGATE (4) 
     LANGUAGE C  
     NO SQL  
     EXTERNAL NAME 'CS!udf_agch002002dyn_udt!udf_agch002002dyn_udt.c'  
     PARAMETER STYLE SQL;

You can then construct a dynamic structured UDT at runtime using the NEW VARIANT_TYPE constructor expression (see “NEW VARIANT_TYPE” in SQL Functions, Operators, Expressions, and Predicates) to pass in the attributes of the type as follows.

     SELECT udf_agch002002dyn_udt(NEW VARIANT_TYPE(tbl_1.a AS a, 
                                                  (tbl_1.b+tbl_1.c) 
                                                   AS b)) 
     FROM tbl_1;

This request constructs a dynamic UDT with two attributes named a and b.

Because udf_agch002002dyn_udt is defined using VARIANT_TYPE for its only parameter, you could just as easily submit the following SELECT request, which the system would resolve by creating a dynamic UDT with three attributes named a, b, and c:

     SELECT udf_agch002002dyn_udt(NEW VARIANT_TYPE(tbl_1.a AS a, 
                                                   tbl_1.b AS b, 
                                                   tbl_1.c AS c)
     FROM tbl_1;

As you can see, by defining the UDF using VARIANT_TYPE as the data type for its input parameter, you can save yourself the trouble of having to create multiple UDFs to resolve overloaded function requests.

You cannot import or export a VARIANT_TYPE UDT, nor can VARIANT_TYPE be cast to a different type.

External routines written in Java do not support UDFs written using the dynamic UDTs VARIANT_TYPE and TD_ANYTYPE. To write such functions, you must write their external routines in C or C++.

By defining a function using TD_ANYTYPE as a parameter or return data type, you can overload the function based on its server character set or numeric precision rather than its name. When you define a function using TD_ANYTYPE, Teradata Database determines the parameter and return 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 function signatures for the same basic function as well as to provide flexibility in coding the logic for the required function behavior.

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

See SQL Data Types and Literals for more information about TD_ANYTYPE and see SQL External Routine Programming for more information about how to code UDFs to take advantage of the TD_ANYTYPE data type.

Teradata Database 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 from one another.

    a BYTEINT

    b SMALLINT

    c INTEGER

    d DECIMALNUMERIC

    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.

    e FLOATDOUBLEPRECISIONREAL

  • 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 from one another. 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.

    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.

    a CHARACTER

    b VARCHARCHARACTERVARYINGLONGVARCHAR

    c CHARACTER LARGE OBJECT

  • The following graphic parameter types are distinct and compatible. Types sharing a bullet are synonyms and are not distinct from one another.
  • 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 from one another 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.