2.20 Function calls

Organization of this section
(1) Function
(2) Format
(3) Operands
(4) Rules
(5) Notes
(6) Rules for determining the function to be called and the data type of the result

(1) Function

A function call calls a specified function.

(2) Format

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

(3) Operands

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.

(4) Rules

  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.

(5) Notes

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

(6) Rules for determining the function to be called and the data type of the result

  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 argumentPriority order
      Numeric dataSMALLINT[Figure] INTEGER[Figure] DECIMAL[Figure] SMALLFLT[Figure] FLOAT
      Character dataCHAR[Figure] VARCHAR
      National character dataNCHAR[Figure] NVARCHAR
      Mixed character dataMCHAR[Figure] MVARCHAR
      Large-object data and binary dataBINARY [Figure]BLOB
    A [Figure] 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 argumentPriority order
    Abstract data typeSame data type [Figure] Super type*
    A [Figure] 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 [Figure] B [Figure] 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.C1Function to be called
Type Af(A)
Type Bf(B)
Type Cf(C)
NULL valuef(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.C1Actual value of T1.C2Function to be called
Type AType Af(A,A)
Type Bf(A,B)
NULL valuef(A,B)
Type BType Af(B,A)
Type Bf(B,C)
NULL valuef(B,C)
Type CType Af(C,A)
Type Bf(C,B)
NULL valuef(C,B)
NULL valueType Af(C,A)
Type Bf(C,B)
NULL valuef(C,B)