Function resolution

Within the database, each function is uniquely identified by its function signature, which is its schema name, function name, the number of parameters, and the data types of the parameters. Thus, a schema can contain several functions that have the same name but each of which have a different number of parameters, or parameters with different data types. Or, a function with the same name, number of parameters, and types of parameters can exist in multiple schemas. When any function is invoked, the database manager must determine which function to execute. This process is called function resolution.

Determining the set of candidate functions:

• Let A be the number of arguments in a function invocation.
• Let P be the number of parameters in a function signature.
• Let N be the number of parameters in a function signature without a defined default.

Candidate functions for resolution of a function invocation are selected based on the following criteria:

• Each candidate function has a matching name and applicable number of parameters. An applicable number of parameters satisfies the condition N ≤ A ≤ P.
• Each parameter of a candidate function that does not have a corresponding argument in the function invocation, specified by either position or name, is defined with a default.
• Each candidate function from a set of one or more schemas has the EXECUTE privilege associated with the authorization ID of the statement invoking the function.

Function resolution is similar for functions that are invoked with a qualified or unqualified function name with the exception that for an unqualified name, the database manager needs to search more than one schema.

• Qualified function resolution: When a function is invoked with a function name and a schema name, the database manager only searches the specified schema for candidate functions.

  If no candidate function in the schema is found, an error is returned. If a function is selected, its successful use depends on it being invoked in a context in which the returned result is allowed. For example, if the function returns an integer data type where a character data type is required, or returns a table where a table is not allowed, an error is returned.

• Unqualified function resolution: When a function is invoked with only a function name, the database manager needs to search more than one schema to resolve the function instance to execute. The SQL path contains the list of schemas to search. For each schema in the SQL path (see SQL path), the database manager selects candidate functions.

  If no candidate function in any schema in the path is found, an error is returned. If a function is selected, its successful use depends on it being invoked in a context in which the returned result is allowed. For example, if the function returns an integer data type where a character data type is required, or returns a table where a table is not allowed, an error is returned.

After the database manager identifies the candidate functions, it selects the candidate with the best fit as the function instance to execute (see Determining the best fit). If more than one schema contains the function instance with the best fit (the function signatures are identical except for the schema name), the database manager selects the function whose schema is earliest in the SQL path.

Function resolution applies to all functions, including built-in functions. Built-in functions logically exist in schema QSYS2. If schema QSYS2 is not explicitly specified in the SQL path, the schema is implicitly assumed at the front of the path. When an unqualified function name is specified, the SQL path must be set to a list of schemas in the desired search order so that the intended function is selected.

In a CREATE VIEW or CREATE TABLE statement, function resolution occurs at the time the view or materialized query table is created. If another function with the same name is subsequently created, the view or materialized query table is not affected, even if the new function is a better fit than the one chosen at the time the view or materialized table was created. In a CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER, CREATE VARIABLE, CREATE MASK, or CREATE PERMISSION statement, function resolution occurs at the time the function, procedure, trigger, variable, mask, or permission is created. The schema of the function that was chosen is saved in the trigger, variable, mask, and permission. It is also saved in functions and procedures for default expressions. If another function with the same name is subsequently created, the function, procedure, trigger, variable, mask, permission, or routine default is only affected if the new function is a better fit than the one chosen at the time the object was created.