The SQSUM2 user-defined aggregate

Table 1 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.

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.

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.

For the tab1 table, which Figure 1 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 Figure 1 defines. This version of SQSUM2 must have the same aggregate support functions as the version that handles INTEGER (see Figure 2).

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.

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

SELECT SQSUM2(col2) FROM tab1;

With the rows that Figure 1 has inserted, the preceding query yields a complexnum_t value of:

ROW(817.96, 1204.09)