Function selection

For both qualified and unqualified function references, the function selection algorithm looks at all the applicable functions, both built-in and user-defined, that have: the given name; the same number of defined parameters as arguments in the function reference; and each parameter identical to or promotable from the type of the corresponding argument.

Applicable functions are functions in the named schema for a qualified reference, or functions in the schemas of the SQL path for an unqualified reference. The algorithm looks for an exact match, or failing that, a best match among these functions. The SQL path is used, in the case of an unqualified reference only, as the deciding factor if two identically good matches are found in different schemas.

Exception: If there is an unqualified reference to a function named RID, and the function is invoked with a single argument that matches a table-reference in the FROM clause of the subselect, the schema is SYSIBM and the built-in RID function is invoked.

You can nest function references, even references to the same function. This is generally true for built-in functions as well as UDFs; however, there are some limitations when column functions are involved.

For example:

     CREATE FUNCTION BLOOP (INTEGER) RETURNS INTEGER ... 
     CREATE FUNCTION BLOOP (DOUBLE) RETURNS INTEGER ...

Now consider the following DML statement:

     SELECT BLOOP( BLOOP(COLUMN1)) FROM T

In this statement, if column1 is a DECIMAL or DOUBLE column, the inner BLOOP reference resolves to the second BLOOP. Because this BLOOP returns an INTEGER, the outer BLOOP resolves to the first BLOOP.

Alternatively in this DML statement, if column1 is a SMALLINT or INTEGER column, the inner bloop reference resolves to the first BLOOP. Because this BLOOP returns an INTEGER, the outer BLOOP also resolves to the first BLOOP. In this case, you are seeing nested references to the same function.

By defining a function with the name of one of the SQL operators, you can actually invoke a UDF using infix notation. For example, suppose you can attach some meaning to the "+" operator for values which have distinct type BOAT. You can define the following UDF:

     CREATE FUNCTION "+" (BOAT, BOAT) RETURNS ...

Then you can write the following valid SQL statement:

     SELECT BOAT_COL1 + BOAT_COL2 
     FROM BIG_BOATS 
     WHERE BOAT_OWNER = 'Nelson Mattos'

But you can also write the equally valid statement:

     SELECT "+"(BOAT_COL1, BOAT_COL2) 
     FROM BIG_BOATS 
     WHERE BOAT_OWNER = 'Nelson Mattos'

Note that you are not permitted to overload the built-in conditional operators such as >, =, LIKE, IN, and so on, in this way.

For a more thorough description of function selection, see the Function References section in the Functions topic listed in the related links.