Hello Sander, > Today i was trying to use the "md5" function in PostgreSQL and found > out that this one is not supported by jOOQ. It looks like there are > quite a few "built in" functions that are not supported. In fact, it's > easier to use advanced functions from postgres "extensions" like > pgcrypto (because they get generated as "routines" by jooq-codegen) > than it is to use some of the simple Postgres-specific builtin > functions. Would it be possible to get code generated for these built- > in functions as if they were user-defined functions?
With Postgres, I think you can generate built-in functions as well. I don't have a Postgres instance running here right now, but I think they are formally defined in some dictionary schema (not sure if pg_catalog or information_schema). Other than that, if you can find a list of useful Postgres-specific built-in functions for me, I'll add them to the PostgresFactory. I've done something similar for MySQL before: https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/util/mysql/MySQLFactory.java In the mean time, if any function is missing, you can always use Factory.function() as a workaround for now. This will be treated as feature request #1138: https://sourceforge.net/apps/trac/jooq/ticket/1138 > Also, PostgreSQL supports functions that return a table (see the > "RETURNS TABLE" stuff at http://www.postgresql.org/docs/9.1/static/sql-createfunction.html > ) . Is there any chance jOOQ could support this too? I've implemented this for Oracle, I guess it's possible to do for Postgres as well. Right now, REF CURSOR types are supported in Postgres. But they're slightly different from TABLE types. I'll see what can be done: https://sourceforge.net/apps/trac/jooq/ticket/1139 Any JDBC-specific hints on this topic are very welcome. CURSOR and TABLE/ARRAY types tend to be quite a pain to correctly integrate with JDBC Cheers Lukas
