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

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.

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.