Example of a user-defined aggregate

The following example uses SPL functions to provide the support functions for a new aggregate, SUMSQ, that calculates the sum of squares. After you register the support functions and create the aggregate, you can use the SUMSQ aggregate with any column that has a data type that casts to a float data type.

CREATE FUNCTION ssq_init (sample float)
   RETURNING float;
   RETURN 0;
END FUNCTION;

CREATE FUNCTION ssq_iter (result float, value float)
   RETURNING float;
   RETURN result + value * value;
END FUNCTION;

CREATE FUNCTION ssq_combine(partial1 float, partial2 float)
   RETURNING float;
   RETURN partial1 + partial2;
END FUNCTION;

CREATE FUNCTION ssq_final(final float)
   RETURNING float;
   RETURN final;
END FUNCTION;

CREATE AGGREGATE sumsq WITH
   (INIT = ssq_init,
    ITER = ssq_iter,
    COMBINE = ssq_combine,
    FINAL = ssq_final);

Now, for example, you can use SUMSQ with the INTEGER column of the c_test table illustrated in Example of extending a built-in aggregate.

SELECT SUMSQ(b) FROM c_test;