Summary of function references

 

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

Applicable functions means functions in the named schema for a qualified reference, or functions in the schemas of the function path for an unqualified reference. The algorithm looks for an exact match, or failing that, a best match among these functions. The current function 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.

An interesting feature is the fact that function references can be nested, 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.

Refining an earlier example:

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

Now consider the following statement:

     SELECT BLOOP( BLOOP(COLUMN1)) FROM T

If COLUMN1 is a DECIMAL or DOUBLE column, the inner BLOOP reference resolves to the second BLOOP defined above. Because this BLOOP returns an INTEGER, the outer BLOOP resolves to the first BLOOP.

Alternatively, if COLUMN1 is a SMALLINT or INTEGER column, the inner BLOOP reference resolves to the first BLOOP defined above. 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. A few additional points important for function references are:

• You can define a function with the name of one of the SQL operators. 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' 

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

• The function selection algorithm does not consider the context of the reference in resolving to a particular function. Look at these BLOOP functions, modified a bit from before:

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

  Now suppose you write the following SELECT statement:

       SELECT 'ABCDEFG' CONCAT BLOOP(SMALLINT_COL) FROM T

  Because the best match, resolved using the SMALLINT argument, is the first BLOOP defined above, the second operand of the CONCAT resolves to data type INTEGER. The statement might not return the expected result since the returned integer will be cast as a VARCHAR before the CONCAT is performed. If the first BLOOP was not present, the other BLOOP is chosen and the statement execution is successful.

• UDFs can be defined with parameters or results having any of the LOB types: BLOB, CLOB, or DBCLOB. The system will materialize the entire LOB value in storage before calling such a function, even if the source of the value is a LOB locator host variable. For example, consider the following fragment of a C language application:

       EXEC SQL BEGIN DECLARE SECTION; 
         SQL TYPE IS CLOB(150K) clob150K ;        /* LOB host var */ 
         SQL TYPE IS CLOB_LOCATOR clob_locator1;  /* LOB locator host var */ 
         char                   string[40];       /* string host var */ 
       EXEC SQL END DECLARE SECTION; 

  Either host variable :clob150K or :clob_locator1 is valid as an argument for a function whose corresponding parameter is defined as CLOB(500K). Referring to the FINDSTRING defined in Example: String search both of the following are valid in the program:

       ... SELECT FINDSTRING (:clob150K, :string) FROM ... 
       ... SELECT FINDSTRING (:clob_locator1, :string) FROM ...

• External UDF parameters or results which have one of the LOB types can be created with the AS LOCATOR modifier. In this case, the entire LOB value is not materialized before invocation. Instead, a LOB LOCATOR is passed to the UDF.

  You can also use this capability on UDF parameters or results which have a distinct type that is based on a LOB. This capability is limited to external UDFs. Note that the argument to such a function can be any LOB value of the defined type; it does not need to be a host variable defined as one of the LOCATOR types. The use of host variable locators as arguments is completely unrelated to the use of AS LOCATOR in UDF parameters and result definitions.

• UDFs can be defined with distinct types as parameters or as the result. DB2^® will pass the value to the UDF in the format of the source data type of the distinct type. Distinct type values that originate in a host variable and which are used as arguments to a UDF which has its corresponding parameter defined as a distinct type must be explicitly cast to the distinct type by the user. There is no host language type for distinct types. DB2's strong typing necessitates this. Otherwise your results may be ambiguous. So, consider the BOAT distinct type that is defined over a BLOB that takes an object of type BOAT as its argument. In the following fragment of a C language application, the host variable :ship holds the BLOB value that is to passed to the BOAT_COST function:

       EXEC SQL BEGIN DECLARE SECTION; 
         SQL TYPE IS BLOB(150K) ship; 
       EXEC SQL END DECLARE SECTION;

  Both of the following statements correctly resolve to the BOAT_COST function, because both cast the :ship host variable to type BOAT:

       ... SELECT BOAT_COST (BOAT(:ship)) FROM ... 
       ... SELECT BOAT_COST (CAST(:ship AS BOAT)) FROM ...

  If there are multiple BOAT distinct types in the database, or BOAT UDFs in other schema, be careful with your function path. Otherwise your results may be unpredictable.

 

Parent topic:

Using UDFs in SQL statements