Aggregate UDAFs are more powerful and more complex to create than scalar UDFs.
After the UDF code has been written and compiled, create a SQL function that invokes the UDF from the appropriate library file, sending the input data to the UDF.
When implementing a UDAF, you must decide:
Whether it will operate only across an entire data set or partition as an online analytical processing (OLAP) -style aggregate, like RANK.
Whether it will operate as either a simple aggregate or an OLAP-style aggregate, like SUM.
Whether it will operate only as a simple aggregate over an entire group.
The declaration and the definition of a UDAF reflects these usage decisions.
The syntax for creating IQ user-defined aggregate functions is:
aggregate-udf-declaration:
CREATE AGGREGATE FUNCTION [ owner.]function-name
( [ parameter , ... ] )
RETURNS data-type
[ aggregate-routine-characteristics ... ]
EXTERNAL NAME library-and-entry-point-name-string
parameter:
param-name data-type [ DEFAULT value ]
aggregate-routine-characteristics:
DUPLICATE { SENSITIVE | INSENSITIVE }
-- is the server allowed to eliminate DISTINCT
| SQL SECURITY {INVOKER | DEFINER}
| OVER restrict
| ORDER order-restrict
-- Must the window-spec contain an ORDER BY?
| WINDOW FRAME
{ { ALLOWED | REQUIRED }
[ window-frame-constraints ... ]
| NOT ALLOWED }
| ON EMPTY INPUT RETURNS { NULL | VALUE }
-- Call or skip function on NULL inputs
window-frame-constraints:
VALUES { [ NOT ] ALLOWED }
| CURRENT ROW { REQUIRED | ALLOWED }
| [ UNBOUNDED ] { PRECEDING | FOLLOWING } restrict
restrict: { [ NOT ] ALLOWED } | REQUIRED
order-restrict:
{ NOT ALLOWED | SENSITIVE | INSENSITIVE | REQUIRED
The handling of the return data type, arguments, data types, and default values are identical to that in the scalar UDF definition.
If a UDAF can be used as a simple aggregate, then it can potentially be used with the DISTINCT qualifier. The DUPLICATE clause in the UDAF declaration determines:
The DUPLICATE INSENSITIVE option allows the optimizer to consider removing the duplicates without affecting the result, giving the optimizer the choice on how to execute the query. Write the UDAF to expect duplicates. If duplicate elimination is required, the server performs it before starting the set of _next_value_extfn calls.
Most of the remaining clauses that are not part of the scalar UDF syntax allow you to specify the usages for this function. By default, a UDAF is assumed to be usable as both a simple aggregate and as an OLAP-style aggregate with any kind of window frame.
For a UDAF to be used only as a simple aggregate function, declare it using:
OVER NOT ALLOWED
Any attempt to then use this aggregate as an OLAP-style aggregate generates an error.
For UDAFs that allow or require an OVER clause, the UDF definer can specify restrictions on the presence of the ORDER BY clause within the OVER clause by specifying “ORDER” followed by the restriction type. Window-ordering restriction types:
REQUIRED – ORDER BY must be specified and cannot be eliminated.
SENSITIVE – ORDER BY may or may not be specified, but cannot be eliminated when specified.
INSENSITIVE – ORDER BY may or may not be specified, but the server can do ordering elimination for efficiency.
NOT ALLOWED – ORDER BY cannot be specified.
Declare a UDAF that makes sense only as an OLAP-style aggregate over an entire set or partition that has been ordered, like the built-in RANK, with:
OVER REQUIRED ORDER REQUIRED WINDOW FRAME NOT ALLOWED
Declare a UDAF that makes sense only as an OLAP-style aggregate using the default window frame of UNBOUNDED PRECEDING to CURRENT ROW, with:
OVER REQUIRED ORDER REQUIRED WINDOW FRAME ALLOWED RANGE NOT ALLOWED UNBOUNDED PRECEDING REQUIRED CURRENT ROW REQUIRED FOLLOWING NOT ALLOWED
The defaults for the all various options and restriction sets are:
DUPLICATE SENSITIVE SQL SECURITY DEFINER OVER ALLOWED ORDER SENSITIVE WINDOW FRAME ALLOWED CURRENT ROW ALLOWED PRECEDING ALLOWED UNBOUNDED PRECEDING ALLOWED FOLLOWING ALLOWED UNBOUNDED FOLLOWING ALLOWED
SQL Security
Defines whether the function is executed as the INVOKER, (the user who is calling the function), or as the DEFINER (the user who owns the function). The default is DEFINER.
When SQL SECURITY INVOKER is specified, more memory is used because each user that calls the procedure requires annotation. Also, when SQL SECURITY INVOKER is specified, name resolution is performed on both the user name and the INVOKER. Qualify all object names (tables, procedures, and so on) with their appropriate owner.
External Name
A function using the EXTERNAL NAME clause is a wrapper around a call to a function in an external library. A function using EXTERNAL NAME can have no other clauses following the RETURNS clause. The library name may include the file extension, which is typically .dll on Windows and .so on UNIX. In the absence of the extension, the software appends the platform-specific default file extension for libraries.
The EXTERNAL NAME clause is not supported for temporary functions. See “Calling external libraries from procedures” in SQL Anywhere Server – Programming.
The IQ server can be started with a library load path that includes the location of the UDF library. On Unix variants, this can be done by modifying the LD_LIBRARY_PATH within the start_iq startup script. While LD_LIBRARY_PATH is universal to all UNIX variants, SHLIB_PATH is preferred on HP, and LIB_PATH is preferred on AIX.
On Unix platforms, the external name specification can contain a fully qualified name, in which case the LD_LIBRARY_PATH is not used. On the Windows platform, a fully qualified name cannot be used and the library search path is defined by the PATH environment variable.