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.

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 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 CHAR or VARCHAR embedded variables with a length of at least 10 bytes
   • 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 8 bytes
   • Time stamp data and literals representing time stamps in character strings
   • Time stamp data and embedded variables corresponding to CHAR or VARCHAR of a length of 19, 20, 22, 24, or 26 bytes
   • Time stamp data and embedded variables corresponding to CHAR or VARCHAR of a length of 19, 20, 22, 24, or 26 bytes or greater
   • 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.
   • BINARY type and hexadecimal character string literals
   In addition, the data type of an argument must have a priority equal to or greater than the data type of the parameter. For the priorities of data types, see Table 2-77 and Table 2-78.
   
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 should 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. If the numbers of authorization identifiers, routine identifiers, and arguments are all the same, but either the data types of the arguments includes the abstract data type or the data types of parameters are not in complete agreement with the order in which the arguments are listed, the function to be called is determined as follows:
   
   • When 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. Table 2-77 shows the priorities of the 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-77 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 basic function.
   The basic 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 a basic function. If the data type of a given function is a predefined data type, the priority listed in Table 2-77 is used. If the data type is an abstract data type, the priority listed in Table 2-78 below is used.
   

   Table 2-78 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 basic 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 (i.e., the basic 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.
   HiRDB checks the compatibility between the data type of the RETURNS clause of the basic function and the RETURNS clause of the candidate functions other than the basic function. 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. The data type and the data length of the result will be the same as the data type and data length of the result of set operations (UNION ALL or EXCEPT ALL). For details, see 2.2 Query expressions.
   In the case of an abstract data type, the abstract data type of the RETURNS clause of the basic function will be used.
   For the BINARY or BLOB type, 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.
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

Basic 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

Basic 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)