Defining the user-defined aggregate

You can define the user-defined aggregate before you create the C implementation of the aggregate support functions. However, you must ensure that the names of the C functions match the names in the CREATE FUNCTION statements that register them.

About this task

To define a user-defined aggregate in a database with SQL

Procedure

Results

The development steps for a UDA list the definition of the UDA after the aggregate support functions are written. However, the CREATE AGGREGATE statement does not verify that the aggregate support functions it lists were registered nor does the CREATE FUNCTION statement verify that the executable C code exists.

The following code fragment shows the CREATE AGGREGATE statement that defines the SQSUM1 user-defined aggregate (which A sample user-defined aggregate describes) in the database.

Figure 1: Registering the SQSUM1 User-Defined Aggregate

CREATE AGGREGATE sqsum1
   WITH (INIT = init_sqsum1,
       ITER = iter_sqsum1,
       COMBINE = combine_sqsum1,
       FINAL = final_sqsum1);

Suppose that the INIT, ITER, COMBINE, and FINAL aggregate support functions for the SQSUM1 aggregate are compiled and linked into a shared-object module named sqsum.

On UNIX™ or Linux™, the executable code for the SQSUM1 aggregate support functions would be in a shared library named sqsum.so.

The following code fragment shows the CREATE FUNCTION statements that register the aggregate support functions for the SQSUM1 user-defined aggregate to handle the INTEGER data type.

Figure 2: Registering the aggregate support functions for SQSUM1 to handle INTEGER

CREATE FUNCTION init_sqsum1(dummy_arg INTEGER)
   RETURNS INTEGER
   WITH (HANDLESNULLS)
   EXTERNAL NAME '/usr/udrs/aggs/sums/sqsum.so'
   LANGUAGE C;

CREATE FUNCTION iter_sqsum1(state INTEGER, 
      one_value INTEGER)
   RETURNS INTEGER
   EXTERNAL NAME '/usr/udrs/aggs/sums/sqsum.so'
   LANGUAGE C;

CREATE FUNCTION combine_sqsum1(state1 INTEGER, 
      state2 INTEGER)
   RETURNS INTEGER
   EXTERNAL NAME '/usr/udrs/aggs/sums/sqsum.so'
   LANGUAGE C;

CREATE FUNCTION final_sqsum1(state INTEGER)
   RETURNS INTEGER
   EXTERNAL NAME '/usr/udrs/aggs/sums/sqsum.so'
   LANGUAGE C;

The registered names of the aggregate support functions must match the names that the CREATE AGGREGATE statement lists. For example, in Registering the aggregate support functions for SQSUM1 to handle INTEGER, a CREATE FUNCTION statement registers the INIT support function as init_sqsum1, which is the same name that the INIT option lists in the CREATE AGGREGATE statement (see Registering the SQSUM1 User-Defined Aggregate).

In addition, the CREATE FUNCTION for the INIT support function must include the HANDLESNULLS routine modifier so that the database server can pass the INIT function the dummy NULL-valued argument.

For more information about the use of the CREATE AGGREGATE and CREATE FUNCTION statements to define user-defined aggregates, see the chapter on aggregates in the Informix® User-Defined Routines and Data Types Developer's Guide. For the syntax of these statements, see the Informix Guide to SQL: Syntax.