How to expose new SQL function in JOOQ

Question

I want to use REGEXP_REPLACE sql function (I'm targeting Postgres), but after quick inspection of DSL class in JOOQ it seems that it is not exposed yet.

How should I approach the problem of adding new function?

I can try extending AbstractFunction, similarly to org.jooq.impl.Replace
Is there any more general way to calling functions not exposed by JOOQ's DSL?

user_521799 · Answer

The REGEXP_REPLACE function has been supported since jOOQ 3.14. In older versions of jOOQ, as always when you're missing out on functionality, use plain SQL templating

public static Field regexpReplace(
    Field in, String pattern, String replacement
) {
    return DSL.field(
        "regexp_replace({0}, {1}, {2})", in.getDataType(), 
        in, 
        DSL.val(pattern), 
        DSL.val(replacement)
    );
}

How to expose new SQL function in JOOQ

1 Answers