I would like to produce SQL that is essentially the equivalent of array[] or [] using jOOQ's DSL so that it's portable between dialects.

Unfortunately in Kotlin, none of these work:

// Overload resolution ambiguity. All these functions match.
// public open fun <T : Any!> array(vararg values: TypeVariable(T)!): Field<Array<(out) TypeVariable(T)!>!> defined in org.jooq.impl.DSL
// public open fun <T : Any!> array(vararg fields: Field<TypeVariable(T)!>!): Field<Array<(out) TypeVariable(T)!>!> defined in org.jooq.impl.DSL
DSL.array()

DSL.array(null as Any?)      // Produces a non-empty array
DSL.array(emptyList<Any>())  // Produces a non-empty array

Is there any way to do this, or something like an DSL.emptyArray() method?

CodePudding user response：

Why passing emptyList<Any> didn't work

The Collection accepting overload of DSL.array() expects a Collection<? extends Field<T>>, i.e. a collection of column expressions, not a Collection<? extends T>, which would be a collection of bind values. We can't have both overloads for the usual type erasure reasons, so only the more generic version is offered. But you're passing a List<Any>, not a List<Field<*>>. You could change your code to:

DSL.array(emptyList<Field<*>>())

Why passing null didn't work

When you pass null, the varargs overload is resolved, which corresponds to ARRAY[NULL] in SQL, an array containing a NULL value.

How to call the varargs overload

If you want to work with the varargs overloads, then just pass an empty array:

DSL.array(arrayOf<String>())

Because array types are reified in Java, both varargs overloads exist:

• array(T...): Accepting bind values
• array(Field<T>...): Accepting column expressoins

Regarding array types

If you're using PostgreSQL, you'll probably have to cast that array expression explicitly to e.g. SQLDataType.VARCHAR.getArrayDataType()

DSL.array(arrayOf<String>()).cast(SQLDataType.VARCHAR.getArrayDataType())

Using bind values as an alternative

You can always use bind values or inline values as well:

DSL.value(arrayOf<String>())
DSL.inline(arrayOf<String>())

In this case, the array type is known to jOOQ, and you don't need to cast it explicitly anymore.