A function call calls a specified function.

function-call::=[authorization-identifier.] routine-identifier
               ([argument [, argument]...])
argument::=value-expression [AS data-type]

authorization-identifier

Specifies the authorization identifier of the function to be called.

When calling a public function, specify PUBLIC, in all caps and enclosed in double quotation marks ("), in the authorization identifier.

routine-identifier

Specifies the routine identifier of the function to be called.

argument::= value-expression [AS data-type]

value-expression

Specifies a value expression for a parameter of the function to be called.

AS data-type

Specifies the ? parameter or a predefined data type for the embedded variable for a parameter of the function to be called.

1. Arguments are associated with parameters in the order in which they are specified.
2. The data type of an argument must be compatible with the data type of the corresponding parameter. For compatible data types, see 1.2.2 Data types that can be converted (assigned or compared). The following combinations of data types are incompatible:
   
   • Character data that uses a different character set
   • Character data and mixed character data
   • Date interval data and literals that express a date interval in decimal
   • Date interval data and embedded variables that are associated with DECIMAL(8,0)
   • Time interval data and literals that express a time interval in decimal
   • Time interval data and embedded variables that are associated with DECIMAL(6,0)
   • Date data and literals that express a date as a character string
   • Date data and embedded variables that are associated with CHAR(10)
   • Date data and embedded variables that are associated with CHAR(20) when the UTF-16 character set is used
   • Date data and CHAR or VARCHAR embedded variables with a length of at least 10 bytes
   • Date data and CHAR or VARCHAR embedded variables that use the UTF-16 character set with a length of at least 20 bytes and that are divisible by two
   • Time data and literals that express a time as a character string
   • Time data and embedded variables that are associated with DECIMAL(6,0)
   • Time data and CHAR or VARCHAR embedded variables with a length of at least eight bytes
   • Time data and CHAR or VARCHAR embedded variables that use the UTF-16 character set with a length of at least 16 bytes and that are divisible by 2
   • Time stamp data and literals representing time stamps in character strings
   • Embedded variable of CHAR or VARCHAR data type greater than or equal to 19 bytes or greater if the fractional second precision of the time stamp data is 0, or 22 bytes or greater if the latter is 2 or greater.
   • Embedded variable of the CHAR or VARCHAR data type that use the UTF-16 character set, are greater than or equal to 38 bytes in length, and that are divisible by 2 if the fractional second precision of the time stamp data is 0, or that are 44 bytes or greater in length and that are divisible by 2 if the precision of the time stamp data is 2 or greater.
   • BINARY type and hexadecimal character string literals
   In addition, the data type of an argument must have a priority greater than or equal to the data type of the parameter. For the priorities of data types, see Table 2-88 and Table 2-89.
   
3. When only the ? parameter or an embedded variable is specified in a value expression, the AS data type must be specified in order to determine the data type for the value expression.
4. When the AS data type is specified, items other than the ? parameter or an embedded variable cannot be specified in a value expression.
5. Unary operations using either the ? parameter or an embedded variable cannot be specified in a value expression.
6. When a repetition column is specified in an argument, a subscript must be specified. However, ANY cannot be specified as the subscript.
7. If only the ? parameter or an embedded variable is specified in a value expression, the ? parameter or an embedded variable must be a simple structure.
8. The maximum number of nesting levels for a function call is 255. The nesting level for a function call is equal to the nesting level specified in the parentheses in "routine-identifier (".
9. In argument, the SUBSTR scalar function producing either BLOB as the data type of the result or the BINARY type with a minimum length of 32,001 bytes cannot be specified as a single value expression.
10. The window function cannot be specified.

For the default values for an authorization identifier, see 1.1.9 Schema path.

1. The specified function is called only if the number of authorization identifiers, the number of routine identifiers, and the number of arguments are all the same, the data types of the arguments do not include the abstract data type, and the data types of parameters are in complete agreement with the order in which arguments are listed. In such a case, the data type of the result of the function will be the data type of the RETURNS clause of the function being called.
2. If there is any disagreement in the numbers of authorization identifiers, routine identifiers, and arguments, the specified function is not called.
3. The following table lists the rules for determining the function to be called.
   

   Table 2-87 Rules for determining the specified contents of the function call and the function to be called

   Specified contents of the function call			Rules for determining the function to be called
   Number of authorization identifiers, routine identifiers, and arguments is the same.	Number of arguments is 0.		Calls the function to make the data types the same for authorization identifiers, routine identifiers, and arguments.
   	Does not include abstract data types in the arguments.	Data types of the parameters are in complete agreement with the order in which arguments are listed.#
   		Data types of the parameters are not in agreement with the order in which arguments are listed.	Follows the rules described in When an abstract data type is not included, below.
   	Includes abstract data type in the arguments.		Follows the rules described in When an abstract data type is included, below.
   Number of authorization identifiers, routine identifiers, and arguments is not the same.			No function can be called, so an error occurs when the SQL statement is analyzed.

   #

   If the data type of the arguments is a character string type, data types are in agreement means that the same character sets are being used.

   • When an abstract data type is not included:
     Based upon the predefined data type of each argument as a standard, beginning with the leftmost argument, either the function whose priority is the same as the standard priority or the function whose parameter has a predefined data type that has the next highest data type priority is called. The table below indicates the priorities of predefined data types. Because the function to be called is determined uniquely during SQL analysis, the data type of the result of the function will be the data type of the RETURNS clause of the function being called.
     

     Table 2-88 Priorities of predefined data types

     Data type of argument	Priority order
     Numeric data	SMALLINT INTEGER DECIMAL SMALLFLT FLOAT
     Character data	CHAR VARCHAR
     National character data	NCHAR NVARCHAR
     Mixed character data	MCHAR MVARCHAR
     Large-object data and binary data	BINARY BLOB

   A B: Priority of A is higher than priority of B.
   
   • When an abstract data type is included:
     If the data types of arguments include the abstract data type, the function to be called is determined as follows:
     
4. Determine the base function.
   The base function is determined as follows: Based upon the predefined data type of each argument as a standard, beginning with the leftmost argument, either the function that has a priority equal to the standard priority or the function whose parameter has a predefined data type that has the next highest data type priority is designated as the base function. If the data type of a given function is a predefined data type, the priority listed in Table 2-88 is used. If the data type is an abstract data type, the priority listed in the following table is used.
   

   Table 2-89 Priorities of abstract data types

   Data type of argument	Priority order
   Abstract data type	Same data type Super type#

   A B: Priority of A is higher than priority of B.

   #: The super type that is specified directly in the UNDER clause in the definition of an abstract data type has higher priority than any other super type.

5. Determine candidate functions.
   If the argument is an abstract data type, the data type of the actual value that the argument can take is either the data type of the abstract data type in which the argument is defined or its subtype. Therefore, in addition to the base function, all functions that have the same data type as the abstract data type of the argument or are associated with parameters with the abstract data type of the subtype will be candidate functions.
   If only one candidate function (that is, the base function) is found, that function is called. The data type of the result of the function will be the data type of the RETURNS clause of the function being called.
   
6. Narrow candidate functions by using the data type of the RETURNS clause.
   If the argument includes an abstract data type, the data type of the RETURNS clause of the base function and the data type of the RETURNS clause of candidate functions other than the base function is checked for compatibility. Functions that have an incompatible RETURNS clause data type are eliminated as candidates. After the compatibility check, the data type of the result of the functions is determined based on the data type of the RETURNS clause of the remaining candidate functions.
   After the compatibility check, the data type of the result of the functions is determined based on the data type of the RETURNS clause of the remaining candidate functions.
   If, after the compatibility check, the data type of the RETURNS clause of the remaining candidate functions is an abstract data type, the abstract data type of the RETURNS clause of the base function is used as the data type of the result.
   If, after the compatibility check, the data type of the RETURNS clause in the remaining candidate functions includes a BINARY or BLOB type function that is 32,001 bytes or longer, the following rules apply.
   
   • The data length of the result is the longest data length.
   • If the BINARY and BLOB types occur on a mixed basis, the result is the BLOB type.
   Otherwise, the data type and length of the result are the same as the data type and length of the result of the set operation (UNION ALL or EXCEPT ALL). For details, see 2.2 Query expressions.
   
7. Determine the function to be used during execution of an SQL statement.
   If steps 2 and 3 above fail to determine uniquely the function to be used, the function to be called from the candidate functions is determined uniquely according to the actual data type of the argument of an abstract data type during execution of an SQL statement. Beginning with the leftmost argument, if the actual value of an argument is NOT NULL, the data type of that value is used as a base. If it is NULL, the data type of that argument is used as a base, and the function that has as a parameter a data type that is equal in priority to the base data type or that is highest in priority among the candidate functions that are lower in priority than the base data type is chosen and designated as the function to be called.
   

Examples

Determine the function to be called when an abstract data type is included

Let A, B, and C denote abstract data types, where C is the super type of B and B is the super type of A (priority of abstract data types: A B C).

Example 1:

Preconditions

Table definition:

CREATE TABLE T1(C1 C)

Function definition:

f(A), f(B), f(C)

SQL statement:

SELECT f(C1) FROM T1

Results

Base function:

f(C)

Candidate functions for which the function call is f(C1):

f(A),f(B),f(C)

Function to be called

The following functions are called during execution of the SQL statement:

Actual value of T1.C1	Function to be called
Type A	f(A)
Type B	f(B)
Type C	f(C)
NULL value	f(C)

Example 2:

Preconditions

Table definition:

CREATE TABLE T1(C1 C,C2 B)

Function definition:

f(A,A), f(A,B), f(A,C), f(B,A), f(B,C), f(C,A), f(C,B), f(C,C)

SQL statement:

SELECT f(C1,C2) FROM T1

Results

Base function:

f(C,B)

Candidate functions for which the function call is f(C1,C2):

f(A,A),f(A,B),f(A,C),f(B,A),f(B,C),f(C,A),f(C,B)

Function to be called

The following functions are called during execution of the SQL statement:

Actual value of T1.C1	Actual value of T1.C2	Function to be called
Type A	Type A	f(A,A)
	Type B	f(A,B)
	NULL value	f(A,B)
Type B	Type A	f(B,A)
	Type B	f(B,C)
	NULL value	f(B,C)
Type C	Type A	f(C,A)
	Type B	f(C,B)
	NULL value	f(C,B)
NULL value	Type A	f(C,A)
	Type B	f(C,B)
	NULL value	f(C,B)