jOOQ query expression type-safety without custom converter

Question

I am trying to extend jOOQ with PostgreSQL's full-text search in type-safe way using Kotlin's extension functions.

My problem is DSL.function doesn't "know" my custom classes/types TsQuery and TsVector and throws an exception. The Function class itself doesn't have a public constructor.

org.jooq.exception.SQLDialectNotSupportedException: Type class jooq.fulltext.TsVector is not supported in dialect DEFAULT

class TsQuery

class TsVector

fun Field<String>.toTsVector(searchConfig: String): Field<TsVector> {
    return DSL.function(
        "to_tsvector",
        TsVector::class.java,
        DSL.inline(searchConfig),
        DSL.coalesce(this, "")
    )!!
}

fun String.toTsQuery(searchConfig: String): Field<TsQuery> {
    return DSL.function(
        "to_tsquery",
        TsQuery::class.java,
        DSL.inline(searchConfig),
        DSL.value(this)
    )!!
}

fun Field<TsVector>.tsMatches(query: Field<TsQuery>): Condition {
    return DSL.condition(
        "{0} @@ {1}",
        this,
        query
    )!!
}

fun Field<TsVector>.tsRank(query: Field<TsQuery>): Field<Double> {
    return DSL.function(
        "ts_rank",
        Double::class.java,
        this,
        query
    )!!
}

If I replace TsQuery and TsVector with String then it works, but I loose typing. I want to use them only for query building, I do not need to be able to parse/convert those types to/from Kotlin.

Answer 1

While you can get this to work using internal classes, such as DefaultDataType (as in your own answer), you should not, as your solution might break in any future minor release, or even patch release.

The proper way to introduce support for new data types in jOOQ is to implement a converter or even better, a binding. A binding will allow you to influence how jOOQ binds variables to prepared statements, and how it reads them from result sets.

Your toTsVector() method would then look like this (excuse any Kotlin errors, I'm a Java guy):

fun Field<String>.toTsVector(searchConfig: String): Field<TsVector> {
    return DSL.function(
        "to_tsvector",
        SQLDataType.OTHER.asConvertedDataType(MyTsVectorBinding()),
        DSL.inline(searchConfig),
        DSL.coalesce(this, "")
    )!!
}

You can now imnplement (I'm using Java for lack of kotlin-fu, but it doesn't matter):

class MyTsVectorBinding implements Binding<Object, TsVector> {
    // ...
}

Answer 2

Creating an object of DefaultDataType directly works even without creating a custom converter.

fun Field<String>.toTsVector(searchConfig: String): Field<TsVector> {

    return DSL.function(
        "to_tsvector",
        DefaultDataType(
            SQLDialect.POSTGRES,
            TsVector::class.java,
            "ts_vector"
        ),
        DSL.inline(searchConfig),
        DSL.coalesce(this, "")
    )!!
}

fun String.toTsQuery(searchConfig: String): Field<TsQuery> {
    return DSL.function(
        "to_tsquery",
        DefaultDataType(
            SQLDialect.POSTGRES,
            TsQuery::class.java,
            "ts_vector"
        ),
        DSL.inline(searchConfig),
        DSL.value(this)
    )!!
}

fun Field<TsVector>.tsMatches(query: Field<TsQuery>): Condition {
    return DSL.condition(
        "{0} @@ {1}",
        this,
        query
    )!!
}

fun Field<TsVector>.tsRank(query: Field<TsQuery>): Field<Double> {
    return DSL.function(
        "ts_rank",
        Double::class.java,
        this,
        query
    )!!
}

I spent way more time on this than I should have and I don't see DefaultDataType mentioned in the jOOQ manual so there might be a better way I don't know about.