Add support for HSQLDB user-defined aggregate functions

[lukaseder]

This is already supported in Oracle. Many other databases also support custom aggregate functions, among which HSQLDB. An example:

CREATE AGGREGATE FUNCTION second_max(
    IN val INTEGER, 
    IN flag BOOLEAN, 
    INOUT highest INTEGER, 
    INOUT second_highest INTEGER
)
RETURNS INTEGER
CONTAINS SQL
BEGIN ATOMIC
  DECLARE temp INTEGER;

  IF flag THEN
    RETURN second_highest;
  ELSE
    SET temp = highest;
    SET highest = GREATEST(COALESCE(highest, -2147483648), val);
    SET second_highest = CASE WHEN temp < highest THEN temp ELSE second_highest END;

    SET temp = GREATEST(COALESCE(second_highest, -2147483648), val);
    SET second_highest = CASE WHEN temp < highest THEN temp ELSE second_highest END; 
  END IF;
END

This can then be used as such:

List<Integer> result1 =
DSL.using(configuration)
   .select(secondMax(BOOK.ID))
   .from(BOOK)
   .groupBy(BOOK.AUTHOR_ID)
   .orderBy(BOOK.AUTHOR_ID.asc())
   .fetch(0, Integer.class);

---

As documented here:
http://hsqldb.org/doc/guide/sqlroutines-chapt.html#src_aggregate_functions