Use $$ in dollar quoted string with jOOQ

Question

I am trying to update a column with jOOQ in a block statement. My Kotlin code looks like this (newName and id are Kotlin variables):

dslContext.begin(
    ...,
    dslContext.update(MYTABLE).set(MYTABLE.NAME, newName).where(MYTABLE.ID.eq(id))
    ...,
).execute()

jOOQ generates SQL like this for PostgreSQL (using jOOQ version 3.18.0):

do
$$
begin
    ...;
    update "public"."mytable" set "public"."mytable"."name" = '...';
    ...;
end;
$$;

which is perfectly ok, unless the new name contains '$$'. I'm getting an 'Unterminated string literal' error, if I'm trying to set the name to something like foo$$bar.

I was able to solve this problem, by splitting the new name by $$ and recreating the original String using DSL.concat(...). It would be easier, if jOOQ would create a dollar quoted string with a tag, e.g. generate something like

do
$FOO$
begin
    ...;
    update "public"."mytable" set "public"."mytable"."name" = '...';
    ...;
end;
$FOO$;

Is this somehow possible?

Answer 1

jOOQ should handle this better, indeed. I've created a feature request for jOOQ 3.19: https://github.com/jOOQ/jOOQ/issues/14800. Starting from jOOQ 3.19, you can specify:

settings.setRenderDollarQuotedStringToken("FOO")

Prior to jOOQ 3.19, this isn't configurable, but you can patch the generated SQL with an ExecuteListener, e.g.

class PostgresDoPatchListener implements ExecuteListener {
    @Override
    public void renderEnd(ExecuteContext ctx) {
        ctx.sql(ctx.sql().replaceAll(
            "do\\s+\\$\\$(.*)\\$\\$", 
            "do \\$FOO\\$$1\\$FOO\\$"
        ));
    }
}