The SQSUM2 user-defined aggregate

A sample user-defined aggregate describes the SQSUM1 aggregate. The SQSUM2 algorithm is the same as SQSUM1:

(x1 + x2 + ... )2

where each x_i is one column value; that is, one aggregate argument.

The following code fragment shows the aggregate support functions that handle an INTEGER argument for the SQSUM2 user-defined aggregate.

/* SQSUM2 ITER support function on INTEGER */
mi_integer iter_sqsum2(state, value, fparam)
   mi_integer state;
   mi_integer value;
   MI_FPARAM *fparam;
{
   /* If 'state' is NULL, this is the first invocation. 
    * Just return 'value'.
    */
   if ( mi_fp_argisnull(fparam, 0) )
      return (value);
   else /* add 'state' and 'value' together */
      return (state + value);
}

/* SQSUM2 COMBINE support function on INTEGER */
mi_integer combine_sqsum2(state1, state2)
   mi_integer state1, state2;
{
   /* Return the new partial sum from two parallel partial
    * sums
    */
   return (iter_sqsum2(state1, state2));
}

/* SQSUM2 FINAL support function on INTEGER */
mi_integer final_sqsum2(state)
   mi_integer state;
{
   /* Calculate square of sum */
   state *= state;

   return (state);
}

In its first invocation, the ITER function performs the state initialization. It then takes a single aggregate argument and adds it to a partial sum. For aggregates with a simple state, the COMBINE function can be the same as the ITER function. Therefore, this COMBINE function just calls iter_sumsq2() to perform the merge of two partial states.

After you have successfully compiled and linked the aggregate support functions, you can define the SQSUM2 aggregate in the database. The following code fragment shows the CREATE AGGREGATE statement that registers the SQSUM2 user-defined aggregate. This statement specifies the registered SQL names of the required aggregate support functions.

CREATE AGGREGATE sqsum2
   WITH (ITER = iter_sqsum2,
       COMBINE = combine_sqsum2,
       FINAL = final_sqsum2);

The following code fragment shows the CREATE FUNCTION statements that register the SQSUM2 aggregate support functions for the aggregate argument of the INTEGER data type.

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

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

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

For the tab1 table, which A table with a complexnum_t column defines, the following query uses the new SQSUM2 aggregate function on the INTEGER column, col3:

SELECT SQSUM2(col3) FROM tab1;

Now, suppose that you want to define the SQSUM2 user-defined aggregate on the complexnum_t named row type, which A named row type to hold a complex number defines. This version of SQSUM2 must have the same aggregate support functions as the version that handles INTEGER (see Registering the SQSUM2 user-defined aggregate).

Aggregate support function	SQL function name	C Function name
ITER	iter_sqsum2()	iter_sqsum2_complexnum()
COMBINE	combine_sqsum2()	combine_sqsum2_complexnum()
FINAL	final_sqsum2()	final_sqsum2_complexnum()

The following code shows the aggregate support functions that handle a complexnum_t named row type as an argument for the SQSUM2 user-defined aggregate:

/* SQSUM2 ITER support function for complexnum_t */
MI_ROW *iter_sqsum2_complexnum(state, value, fparam)
   MI_ROW *state;
   MI_ROW *value;
   MI_FPARAM *fparam;
{
   /* Compute the new partial sum using the complex_plus() 
    * function. Put the sum in a new MI_ROW, which 
    * complex_plus() allocates (and returns a pointer to)
    */
   return (complex_plus(state, value, fparam));
}

/* SQSUM2 COMBINE support function for complexnum_t */
MI_ROW *combine_sqsum2_complexnum(state1, state2, fparam)
   MI_ROW *state1, *state2;
   MI_FPARAM *fparam;
{
   MI_ROW *ret_state;

   ret_state = 
      iter2_sqsum2_complexnum(state1, state2, fparam);

   mi_free(state1);
   mi_free(state2);

   return (ret_state);
}

/* SQSUM2 FINAL support function for complexnum_t */
MI_ROW *final_sqsum2_complexnum(state)
   MI_ROW *state;
{
   MI_CONNECTION *conn;
   MI_TYPEID *type_id;
   MI_ROW_DESC *row_desc;

   MI_ROW *ret_row;
   MI_DATUM values[2];
   mi_boolean nulls[2] = {MI_FALSE, MI_FALSE};

   mi_real *real_value, *imag_value;
   mi_integer real_len, imag_len;
   mi_real sqsum_real, sqsum_imag;

   /* Extract complex values from state row structure */
   mi_value_by_name(state, "real_part", 
      (MI_DATUM *)&real_value, &real_len);
   mi_value_by_name(state, "imaginary_part", 
      (MI_DATUM *)&imag_value, &imag_len);

   /* Calculate square of sum */
   sqsum_real = (*real_value) * (*real_value);
   sqsum_imag = (*imag_value) * (*imag_value);

   /* Put final result into 'values' array */
   values[0] = (MI_DATUM)&sqsum_real;
   values[1] = (MI_DATUM)&sqsum_imag;

   /* Generate return row type */
   conn = mi_open(NULL, NULL, NULL);
   type_id = mi_typestring_to_id(conn, "complexnum_t");
   row_desc = mi_row_desc_create(type_id);
   ret_row = mi_row_create(conn, row_desc, values, nulls);

   return (ret_row);
}

The following code fragment shows the CREATE FUNCTION statements that register the SQSUM2 aggregate support functions for an aggregate argument of the complexnum_t data type.

CREATE FUNCTION iter_sqsum2(state complexnum_t, 
      one_value complexnum_t)
   RETURNS complexnum_t
   WITH (HANDLESNULLS)
   EXTERNAL NAME
'/usr/udrs/aggs/sums/sqsum.so(iter_sqsum2_complexnum)'
   LANGUAGE C;

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

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

The following query uses the SQSUM2 aggregate function on the complexnum_t column, col2:

SELECT SQSUM2(col2) FROM tab1;

With the rows that A table with a complexnum_t column has inserted, the preceding query yields a complexnum_t value of:

ROW(817.96, 1204.09)