where
each xi is one column value; that is, one aggregate
argument.
However, the SQSUM2 aggregate takes advantage of the fact
that this aggregate has a simple state. Because the database server
automatically handles state management, the SQSUM2 aggregate can safely
omit the INIT function.
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.
Tip: The ITER function in
the preceding code fragment can use the binary operator plus() to
perform the addition. This operator is already defined on the INTEGER
data type and therefore would not need to be written or registered.
To use plus() in ITER, you would need to ensure that it is
defined for the data type on which the SQSUM2 aggregate is defined.
The data type of the aggregate result is also the same
as the aggregate state. Therefore, SQSUM2 is a simple binary operator
and the FINAL support function is not needed to convert the data type
of the final state. However, the SQSUM2 aggregate still does require
a FINAL support function. The SQSUM2 algorithm involves a post-iteration
calculation: it must square the final sum to obtain the aggregate
return value. The FINAL function performs this final calculation and
returns it as the aggregate result for the SQSUM2 aggregate.
Suppose that the ITER, COMBINE, and FINAL aggregate support
functions for the SQSUM2 aggregate are compiled and linked into a
shared-object module named sqsum.
On UNIX™ or Linux™, the executable code for
the SQSUM2 aggregate support functions would be in a shared library
named sqsum.so.
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 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;
With the rows that A
table with a complexnum_t column has
inserted, the preceding query yields an INTEGER value of 10201,
which is the same value that the SQSUM1 aggregate returned for these
same rows.
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: