How to pass array into parameters of function

Question

I try to create alias in H2 for function with two parameters of array.

parameters: Array sourceArray, Array subArray Returns boolean: TRUE if sourceArray contains all elements of subArray. OR ELSE false

I declared function:

CREATE ALIAS IF NOT EXISTS array_contains_all AS '
     boolean arrayContainsAll(java.util.HashSet sourceArray, java.util.HashSet subArray) {
           return sourceArray.containsAll(subArray);

}';

I call this function:

SELECT array_contains_all (Array['HELLO',  'WORLD', 'FRIEND'],  Array['HELLO', 'WORLD']);

I expected TRUE. But it throws error:

Hexadecimal string contains non-hex character: "[HELLO, WORLD, FRIEND]".

Then I tried:

CREATE ALIAS IF NOT EXISTS array_contains_all AS '
     boolean arrayContainsAll(java.sql.Array sourceArray, java.sql.Array subArray) {
     java.util.List<Object> sourceArrayList = java.util.Arrays.asList((Object[]) sourceArray.getArray());
     java.util.List<Object> subArrayList  = java.util.Arrays.asList((Object[]) subArray.getArray());
     return sourceArrayList.containsAll(subArrayList);

}';

It throws the same error:

Hexadecimal string contains non-hex character: "[HELLO, WORLD, FRIEND]".

How to correct this?

Answer 1

You need to upgrade your version of H2 to 2.*.* and declare a function with Java arrays with some exact data type of elements:

CREATE ALIAS IF NOT EXISTS ARRAY_CONTAINS_ALL AS '
import java.util.*;
@CODE
Boolean arrayContainsAll(String[] sourceArray, String[] subArray) {
    if (sourceArray == null || subArray == null) {
        return null;
    }
    if (subArray.length == 0) {
        return true;
    }
    HashSet<String> subSet = new HashSet<>(Arrays.asList(subArray));
    if (subSet.contains(null)) {
        return null;
    }
    HashSet<String> sourceSet = new HashSet<>(Arrays.asList(sourceArray));
    if (sourceSet.containsAll(subSet)) {
        return true;
    }
    if (sourceSet.contains(null)) {
        return null;
    }
    return false;
}
';

If you need to support multiple data types, you can try to write a Java class with methods with the same name and with different parameters, put it into classpath of H2 and load them with CREATE ALIAS … FOR 'package.class.method'.

Really old versions of H2 have weird implementation of ARRAY data type not compatible with anything. In these versions you can only mess with internal classes of H2, it can pass arrays to user-defined functions only as org.h2.value.Value.