JOOQ Parser Issue (POSTGRES to H2 Translation)-CodePudding

I am trying to convert a POSTGRES sql to H2 using jooq api in Java. I am new to this library. Can anyone tell me what am I doing wrong here ? Here is my code:

Query query = DSL
    .using(SQLDialect.POSTGRES)
    .parser()
    .parseQuery("select split_part(some_field, '@', 2) from test");
String convertedQuery = DSL.using(SQLDialect.H2).render(query));

I am using jooq dependency version 3.13.6 Same sql when tried from https://www.jooq.org/translate/ works perfetly, but my code throws following exception:

java.lang.Exception: Unknown function: [1:21] SELECT SPLIT_PART ( []OPACKET_SC_EMAILADDRESSDECRYPTED , '@' , 2 ) FROM TEST MT*

I would like to enable "Parse unknown functions" from code, how can I achieve that?

CodePudding user response：

I figured how to enable "Parse unknown functions", however some functions like date_diff & date_parse aren't being recognized. Any clues?

CodePudding user response：

The jOOQ ParseListener SPI can be used to add new functionality to jOOQ's parser, such as in your case:

Query query = configuration
    .derive(ParseListener.onParseCondition(ctx -> {
        if (ctx.parseFunctionNameIf("SPLIT_PART")) {
            ctx.parse('(');
            Field<?> f1 = ctx.parseField();
            ctx.parse(',');
            Field<?> f2 = ctx.parseField();
            ctx.parse(',');
            Field<?> f3 = ctx.parseField();
            ctx.parse(')');

            return ... // implement your emulation here
        }

        // Let the parser take over if we don't know the token
        return null;
    })
    .dsl()
    .parser()
    .parseQuery("select split_part(some_field, '@', 2) from tes");

This feature requires the jOOQ 3.15 commercial editions