Function Overloading - Advanced SQL Engine - Teradata Database

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

Vantage 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. For more information about function overloading, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.

Vantage 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 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. See NEW VARIANT_TYPE in Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

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, Vantage 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 Teradata Vantage™ - Data Types and Literals, B035-1143 for more information about TD_ANYTYPE and see Teradata Vantage™ - SQL External Routine Programming, B035-1147 for more information about how to code UDFs to take advantage of the TD_ANYTYPE data type.