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 (dummy 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;

Using User-Defined Data Types with User-Defined Aggregates

You cannot use SUMSQ with the complex column of the c_test table illustrated in Example of Extending a Built-In Aggregate because the complex data type does not cast to the FLOAT data type. To use SUMSQ with the complex data type, you must overload the support functions of the SUMSQ aggregate.

CREATE FUNCTION ssq_init (dummy complex)
   RETURNING complex;
   RETURN ROW(0,0)::complex;
END FUNCTION;

CREATE FUNCTION ssq_iter (partial complex, c complex)
   RETURNING complex;
   RETURN ROW (
      (partial.real + c.real*c.real - c.imag*c.imag),
      (partial.imag + 2*c.real*c.imag) 
      )::complex;
END FUNCTION;

CREATE FUNCTION ssq_combine(p1 complex, p2 complex)
   RETURNING complex;
   RETURN ROW(p1.real + p2.real, 
           p1.imag + p2.imag)::complex;
END FUNCTION;

CREATE FUNCTION ssq_final(final complex)
   RETURNING complex;
   RETURN final::complex;
END FUNCTION;

When you overload support functions for a user-defined aggregate, you must prepare exactly the same functions as those declared in the CREATE AGGREGATE statement. In this example, that requirement means overloading each of the support functions.

Omitting Support Functions

For completeness, the preceding examples show all four support functions: INIT, ITER, COMBINE, and FINAL. Because SUMSQ is a simple aggregate, the examples could have omitted the INIT and FINAL functions. You could use the following commands to create the SSQ2 aggregate:

CREATE FUNCTION ssq2_iter (result float, opr float)
   RETURNING float;
   IF result IS NULL THEN
     LET result = (opr*opr);
   ELSE
     LET result = result + opr*opr;
   END IF
   RETURN result;
END FUNCTION;

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

CREATE AGGREGATE ssq2 WITH
   (ITER = ssq2_iter,
    COMBINE = ssq2_combine);

Difference Between SUMSQ and SSQ2 Aggregates

The INIT function for SUMSQ explicitly initializes the state; that is, the result. Because the SSQ2 aggregate does not include an INIT function, the ITER function must explicitly handle the case where the result is null.

The behavior of the SSQ2 aggregate is not exactly the same as that of the SUMSQ aggregate. You can use SSQ2 only with a column of the FLOAT data type unless you explicitly cast the column to FLOAT. In the following example, the first SELECT statement fails, but the other SELECT statements succeed:

CREATE TABLE trial (t INT);
  INSERT INTO trial VALUES (2);
  INSERT INTO trial VALUES (3);
SELECT ssq2(t) FROM trial;           -- fails
SELECT ssq2(t::float) FROM trial;    -- succeeds
SELECT sumsq(t) from trial;          -- succeeds

Because the INIT function was omitted from the declaration of SSQ2, the aggregate uses the data type of the aggregate argument as its state type. The ITER function expects a FLOAT data type. Thus, when the INIT function is omitted, the aggregate argument must be a FLOAT data type. For more about the state type, refer to Resolving the Support Functions.

Overloading the Support Functions for SSQ2

Because any overloaded functions must be the same as those in the declaration of the aggregate, you must overload ssq2_iter and ssq2_combine to extend the SSQ2 aggregate to the complex data type.

CREATE FUNCTION ssq2_iter (partial complex, c complex)
   RETURNING complex;
   RETURN ROW (
      (partial.real + c.real*c.real - c.imag*c.imag),
      (partial.imag + 2*c.real*c.imag) 
      )::complex;
END FUNCTION;

CREATE FUNCTION ssq2_combine(p1 complex, p2 complex)
   RETURNING complex;
   RETURN ROW(p1.real + p2.real, 
           p1.imag + p2.imag)::complex;
END FUNCTION;