Function invocation

Each reference to a scalar or aggregate function (either built-in or user-defined) conforms to the following syntax:

Read syntax diagramSkip visual syntax diagramfunction-name(ALLDISTINCT,expressionTABLEtransition-table-name)

In the above syntax, expression cannot include an aggregate function. See Expressions for other rules for expression.

The ALL or DISTINCT keyword can only be specified for an aggregate function or a user-defined function that is sourced on an aggregate function.

When a function is invoked within a trigger body, the TABLE keyword can be specified to indicate that an argument is a trigger transition table. In this case, the corresponding parameter of the function must have been defined with the TABLE LIKE clause.

Table functions can be referenced only in the FROM clause of a subselect. For more information on referencing a table function, see the description of the from-clause.

Start of changeAn array can only be specified as an argument to a function for a parameter that is defined with an array type. An array element specifies a scalar value, and can therefore be specified as an argument to a function when the data type of the array element is promotable to the data type of the corresponding parameter of the function definition.End of change

When the function is invoked, the value of each of its parameters is assigned using storage assignment, to the corresponding parameter of the function. Control is passed to external functions according to the calling conventions of the host language. When execution of a user-defined aggregate or scalar function is complete, the result of the function is assigned, using storage assignment, to the result data type. For information about assignment rules, see Assignment and comparison.

Additionally, a character FOR BIT DATA argument cannot be passed as input for a parameter that is not defined as character FOR BIT DATA. Likewise, a character argument that is not FOR BIT DATA cannot be passed as input for a parameter defined as character FOR BIT DATA.

For compatibility with other SQL implementations, UNIQUE can be specified as a synonym for DISTINCT in aggregate functions.