Declaring a user-defined aggregate function in Sybase Central

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.

1. In Sybase Central, connect to the database as a user with DBA or Resource authority.
2. In the left pane, right-click Procedures & Functions and select New > Function.
3. In the Welcome dialog, type a name for the function and select which user will be the owner of the function.
4. To create a user-defined function, select External C/C++. Click Next.
5. Select Aggregate.
6. Type the name of the dynamically linkable library file, omitting the .so or .dll extension.
7. Type a name for the descriptor function. Click Next.
8. Select the type of value to be returned in the function, and specify the size, units, and scale for the value. Click Next.
9. Select whether the privileges used for running the function are from the defining user (definer) or the calling user (invoker).
10. Specify whether the function is allowed to be, required to be, or 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.

11. Specify if the function requires the user of an ORDER BY clause when it is used to define a window. Click Next.
12. 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, skip to step 14.

13. Identify the constraints on the WINDOW FRAME clause. Click Next.
14. Specify if duplicate input values need to be filtered out by the database server prior to calling the function.
15. Identify if the return value of the function is NULL or a fixed value when it is called with no data. Click Next.
16. Add a comment describing the purpose of the new function. Click Finish.
17. In the right pane, click the SQL tab to complete the procedure code.

    The new function appears in Procedures & Functions.