How to write a function in the H2 database without using Java

Question

This question is reference to question "How to create stored procedure using H2 database?" (continuation of the same question).

Is there a way to write a function in H2 without using Java code? My requirement is to translate the functions written in SQL to H2 without using Java code. I found lot of examples in different portals doing the same using Java code. Your help will be greatly appreciated.

Regards Arun

Answer 1

Currently, H2 does only support functions written in Java or a related language (for example Groovy or Scala). PL/SQL (Oracle) and T-SQL (MS SQL Server, Sybase) are not supported.

Answer 2

If your primary goal is to run SQL (or SQLesque) statements within your H2 Java user-defined functions, jOOQ could be an option as a "PL/Java" implementation. Of course, this would still be a Java solution.

An example of such a function can be seen in this blog post:

http://blog.jooq.org/2011/11/04/use-jooq-inside-your-h2-database

public class Functions {
    public static int countBooks(Connection connection, Integer authorId) 
    throws SQLException {
        // Translate your T-SQL statements to jOOQ statements
        return DSL.using(connection, SQLDialect.H2)
                  .selectCount()
                  .from(BOOK)
                  .where(BOOK.AUTHOR_ID.eq(authorId))
                  .fetchOne(0, int.class);
    }
}

Declare the above method as an ALIAS to H2

CREATE ALIAS countBooks 
   FOR "org.example.Functions.countBooks";

Use the function in SQL

SELECT author.last_name, countBooks(author.id) 
FROM author

A similar approach can be taken with H2's own SQL abstraction JaQu, of course. Using JaQu wouldn't add any additional dependency, I think.