Sybase IQ supports user-defined aggregate functions (UDAFs).
The SUM function is an example of a built-in aggregate function.
A simple aggregate function takes a set of argument values and produces
a single result value from that set of inputs. User-defined aggregate
functions can be written that can be used anywhere the SUM aggregate
can be used.
-
In Sybase Central, connect to the database as a user with
DBA or Resource authority.
-
In the left pane, right-click Procedures &
Functions and select New > Function.
-
In the Welcome dialog, type a name for the function
and select which user will be the owner of the function.
-
To create a user-defined function, select External
C/C++. Click Next.
-
Select Aggregate.
-
Type the name of the dynamically linkable library file, omitting
the .so or .dll extension.
-
Type a name for the descriptor function. Click
Next.
-
Select the type of value to be returned in the
function, and specify the size, untis, and scale for the value.
Click Next.
-
Select whether the privileges used for running
the function are from the defining user (definer) or the calling
user (invoker).
-
Specify if the function is allowed to be used
in an OVER clause, is required to be used in
an OVER clause, or is not allowed to be used
in an OVER clause. Click Next.
If the function is not allowed to be used in an OVER clause,
proceed with step 14.
-
Specify if the function requires the user of an ORDER
BY clause when it is used to define a window. Click Next.
-
Specify if the function is allowed to be used
in a WINDOW FRAME clause, is required to be used
in an WINDOW FRAME clause, or is not allowed
to be used in a WINDOW FRAME clause. Click Next.
If the function is not allowed to be used in a WINDOW
FRAME clause, proceed with step 14.
-
Identify the constraints on the WINDOW
FRAME clause. Click Next.
-
Specify if duplicate input values need to be filtered
out by the database server prior to calling the function.
-
Identify if the return value of the function is NULL or
a fixed value when it is called with no data. Click Next.
-
Add a comment describing the purpose of the new
function. Click Finish.
-
In the right pane, click the SQL tab to complete
the procedure code.
The new function appears in Procedures & Functions.