There might be more than one function with the same name that is a candidate for execution. In that case, the database manager determines which function is the best fit for the invocation by comparing the argument and parameter data types. Note that the data type of the result of the function or the type of function (aggregate, scalar, or table) under consideration does not enter into this determination.
If the data types of all the parameters for a given function are the same as those of the arguments in the function invocation, that function is the best fit. If there is no exact match, the database manager compares the data types in the parameter lists from left to right, using the following method:
The following examples illustrate function resolution.
Example 1: Assume that MYSCHEMA contains two functions, both named FUNA, that were created with these partial CREATE FUNCTION statements.
CREATE FUNCTION MYSCHEMA.FUNA (VARCHAR(10), INT, DOUBLE) ... CREATE FUNCTION MYSCHEMA.FUNA (VARCHAR(10), REAL, DOUBLE) ...
Also assume that a function with three arguments of data types VARCHAR(10), SMALLINT, and DECIMAL is invoked with a qualified name:
MYSCHEMA.FUNA( VARCHARCOL, SMALLINTCOL, DECIMALCOL ) ...
Both MYSCHEMA.FUNA functions are candidates for this function invocation because they meet the criteria specified in Function resolution. The data types of the first parameter for the two function instances in the schema, which are both VARCHAR, fit the data type of the first argument of the function invocation, which is VARCHAR, equally well. However, for the second parameter, the data type of the first function (INT) fits the data type of the second argument (SMALLINT) better than the data type of second function (REAL). Therefore, the database manager selects the first MYSCHEMA.FUNA function as the function instance to execute.
Example 2: Assume that functions were created with these partial CREATE FUNCTION statements:
1. CREATE FUNCTION SMITH.ADDIT (CHAR(5), INT, DOUBLE) ... 2. CREATE FUNCTION SMITH.ADDIT (INT, INT, DOUBLE) ... 3. CREATE FUNCTION SMITH.ADDIT (INT, INT, DOUBLE, INT) ... 4. CREATE FUNCTION JOHNSON.ADDIT (INT, DOUBLE, DOUBLE) ... 5. CREATE FUNCTION JOHNSON.ADDIT (INT, INT, DOUBLE) ... 6. CREATE FUNCTION TODD.ADDIT (REAL) ... 7. CREATE FUNCTION TAYLOR.SUBIT (INT, INT, DECIMAL) ...
Also assume that the SQL path at the time an application invokes a function is "TAYLOR", "JOHNSON", "SMITH". The function is invoked with three data types (INT, INT, DECIMAL) as follows:
SELECT ... ADDIT(INTCOL1, INTCOL2, DECIMALCOL) ...
Function 5 is chosen as the function instance to execute based on the following evaluation:
Example 3: Assume that functions were created with these partial CREATE FUNCTION statements:
1. CREATE FUNCTION BESTGEN.MYFUNC (INT, DECIMAL(9,0)) ... 2. CREATE FUNCTION KNAPP.MYFUNC (INT, NUMERIC(8,0))... 3. CREATE FUNCTION ROMANO.MYFUNC (INT, FLOAT) ...
Also assume that the SQL path at the time an application invokes a function is "ROMANO", "KNAPP", "BESTGEN". The function is invoked with two data types (SMALLINT, DECIMAL) as follows:
SELECT ... MYFUNC(SINTCOL1, DECIMALCOL) ...
Function 2 is chosen as the function instance to execute based on the following evaluation:
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.