[h2] Adding a new aggregate function

[Tushar Iyer]

Hi all,

I'm new to the H2 community and I want to get acquainted with tailoring it
by attempting to try and add two new aggregate functions. From what I
understand, I need to write the java file to implement the interface at
org.h2.api.AggregateFunction. I was looking through the list of currently
built aggregate functions on the H2 website here
<http://www.h2database.com/html/functions.html> and thought I would try and
implement aggregate functions for '*Median*' and '*Mode*'. However, when
looking through the H2 src (Which I got from their GitHub as of Nov.14th,
2018), I found a file '*Aggregate*' in org.h2.expression.aggregate. That
file in turn extends '*AbstractAggregate*' also found in the same
directory. Neither uses the '*AggregateFunction*' interface. My concerns
are as follows:


1. The new '*Aggregate*' file already has declarations for '*Median*'
and '*Mode*', so is there a way I can still implement my own?
2. I've read through some of the other google posts, but I'm still
unsure as to how I define the SQL commands in the java class file such that
I can call my own aggregate functions
3. Assuming that I still do need to use the original interface, would I
place my .java files in the same directory?
1. If I should *not* use the original interface, can someone please
point me in the direction of the files I *should* be referencing?

Thank you all for your help!

[Noel Grandin]

have a look at
org.h2.test.db.TestFunctions, it has some tests that exercise use of AggregateFunction.


you need to make sure your new aggregate is on H2's classpath, that is how we find it, and then you tell H2 about it using
CREATE AGGREGATE
see
http://h2database.com/html/grammar.html#create_aggregate

[Tushar Iyer]

Hi Noel,

Thank you for the reference! I'll definitely make use of the TestFunctions
file. I would like to know, would I potentially be able to write my
function *in that file itself* and have it be usable after compiling H2?

Post by Noel Grandin
have a look at
org.h2.test.db.TestFunctions, it has some tests that exercise use of AggregateFunction.
you need to make sure your new aggregate is on H2's classpath, that is how
we find it, and then you tell H2 about it using
CREATE AGGREGATE
see
http://h2database.com/html/grammar.html#create_aggregate

[Evgenij Ryazanov]

Hello.

Both org.h2.api.AggregateFunction and better org.h2.api.Aggregate
interfaces are for user-defined aggregate functions. They should not be
placed inside H2 sources and do not require modification of H2. You need to
write and compile your implementation of one of these interfaces, place it
in classpath of your application and register it with CREATE AGGREGATE
command under some name that will not conflict with built-in functions. H2
will load it for this specific database.

If you want to add some aggregate function to H2 itself take a look on
org.h2.expression.aggregate.Aggregate class instead. However, H2 has both
MEDIAN and MODE aggregates, so choose some missing functionality instead.

[Tushar Iyer]

Hi,

Okay I think I understand. Just to be fully clear, I have outlined what I
believe to be the procedure below. Can you confirm that it works?


- I will be writing my classes using Netbeans, of which two of the java
files will be structured as below**. The other two would just be
implementations of the AggregateFunction interface
- Compile the class files with javac
- Place the class files inside the jar

**

CREATE ALIAS MY_FUNCTION AS $$
String myFunc(String value) {
return new String(value).funcMethods().toString();
}
$$;

Post by Evgenij Ryazanov
Hello.
Both org.h2.api.AggregateFunction and better org.h2.api.Aggregate
interfaces are for user-defined aggregate functions. They should not be
placed inside H2 sources and do not require modification of H2. You need to
write and compile your implementation of one of these interfaces, place it
in classpath of your application and register it with CREATE AGGREGATE
command under some name that will not conflict with built-in functions. H2
will load it for this specific database.
If you want to add some aggregate function to H2 itself take a look on
org.h2.expression.aggregate.Aggregate class instead. However, H2 has both
MEDIAN and MODE aggregates, so choose some missing functionality instead.