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. When determining whether the data types
of the parameters are the same as the arguments:
Synonyms of data types match. For example, DOUBLE
and FLOAT are considered to be the same.
- Attributes of a data type such as length, precision, scale, and
CCSID are ignored. Therefore, CHAR(8) and CHAR(35) are considered
to be the same, as are DECIMAL(11,2), and DECIMAL(4,3).
- The character and graphic types are considered to be the same.
For example, the following are considered to be the same type: CHAR
and GRAPHIC, VARCHAR and VARGRAPHIC, and CLOB and DBCLOB. CHAR(13)
and GRAPHIC(8) are considered to be the same type.
If there is no match, the database manager compares the
data types in the parameter lists from left to right, using the following
method:
- Compare the data type of the first argument in the function invocation
to the data type of the first parameter in each function. (The rules
previously mentioned are used to determine whether the data types
are the same.)
- For this argument, if one function has a data type that fits the
function invocation better than the data types in the other functions,
that function is the best fit. The precedence list for the promotion
of data types in Promotion of data types shows the data
types that fit each data type in best-to-worst order.
- If the data type of the first parameter for more than one candidate
function fits the function invocation equally well, repeat this process
for the next argument of the function invocation. Continue for each
argument until a best fit is found.
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:
- Function 6 is eliminated as a candidate because schema TODD is
not in the SQL path.
- Function 7 in schema TAYLOR is eliminated as a candidate because
it does not have the correct function name.
- Function 1 in schema SMITH is eliminated as a candidate because
the INT data type is not promotable to the CHAR data type of the first
parameter of Function 1.
- Function 3 in schema SMITH is eliminated as a candidate because
it has the wrong number of parameters.
- Function 2 is a candidate because the data types of its parameters
match or are promotable to the data types of the arguments.
- Both Function 4 and 5 in schema JOHNSON are candidates because
the data types of their parameters match or are promotable to the
data types of the arguments. However, Function 5 is chosen as the
better candidate because although the data types of the first parameter
of both functions (INT) match the first argument (INT), the data type
of the second parameter of Function 5 (INT) is a better match of the
second argument (INT) than the data type of Function 4 (DOUBLE).
- Of the remaining candidates, Function 2 and 5, the database manager
selects Function 5 because schema JOHNSON comes before schema SMITH
in the SQL path.
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, NUMERIC(8,0))...
4. CREATE FUNCTION ROMANO.MYFUNC (INT, FLOAT) ...
Also assume that the SQL path at the time an application
invokes a function is "ROMANO", "KNAPP", "BESTGEN" and that the authorization
ID of the statement has the EXECUTE privilege to Functions 1, 2, and
4. 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:
- Function 3 is eliminated. It is not a candidate for this function
invocation because the authorization ID of the statement does not
have the EXECUTE privilege to the function. The remaining three functions
are candidates for this function invocation because they meet the
criteria specified in Function resolution.
- Function 4 in schema ROMANO is eliminated because the second parameter
(FLOAT) is not as good a fit for the second argument (DECIMAL) as
the second parameter of either Function 1 (DECIMAL) or Function 2
(NUMERIC).
- The second parameters of Function 1 (DECIMAL) and Function 2 (NUMERIC)
are equally good fits for the second argument (DECIMAL).
- Function 2 is finally chosen because "KNAPP" precedes "BESTGEN"
in the SQL path.