Postgresql Text[]

[skch-17]

Hello and a big thank you for all the hard work done so far.

I have a question or maybe a bug report / fix. Not sure yet what it is.

If i use the text[] type provided by the postgresql package, i am unable to set data to my where statement. In code i dont see which operators should work out of the box with the array type. So i wrote an extension:

It's more or less a copy of the hstore class.

I set the param like this:
.whereWithConditions {it += foldersTable.fkClient eq clientId filterObject.mapParameters()?.apply { it += this } it += documentsTable.tags.overlap(arrayOf("Offerten")) }

It will not allow me to set the paramter. However, if i change the setParameter to:
override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: TextArray) { ps.setArray(index, ps.connection.createArrayOf("text",parameter)) }
It works.

So:

1. What i am i doing wrong?
2. Is it a bug?
3. Do the @> and && operators not exist yet*?

*i am happy to provide my extension class code to help

[vincentlauvlwj]

Could you provide more code for debug?

[skch-17]

Hi vincentlauvlwj

ERROR: operator does not exist: text[] @> character varying[]
  Hinweis: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 1132

It must have a problem with the type cast in the Filter logic i wrote. But the debuger tells me that filterValue.value is a String[]:

infix fun ColumnDeclaring<TextArray>.applyList(filterValue: FilterValue): ScalarExpression<Boolean> {
    //The other types should be fine.
    return when (filterValue.filterType) {
        FilterType.CO -> contains(filterValue.value as TextArray)
        FilterType.OV -> overlap(filterValue.value as TextArray)
        else -> throw IllegalStateException("Not supported filter type: ${filterValue.filterType}")
    }
}

By the way if you like here is the code for the psql text array opertors:

`/**
 * Enum for `text array` operators.
 */
enum class ArrayExpressionType(private val value: String) {

    /**
     * Array overlap operator, translate to the && operator in PostgreSQL.
     */
    OVERLAP("&&"),

    /**
     * Array contains operator, translated to the @> operator in PostgreSQL.
     */
    CONTAINS("@>"),

    /**
     * Array contained in operator, translated to the <@ operator in PostgreSQL.
     */
    CONTAINED_IN("<@");


    override fun toString(): String {
        // Escape for JDBC prepared statements.
        return value.replace("?", "??")
    }
}


/**
 * Expression class represents PostgreSQL's `array` operations.
 *
 * @property type the expression's type.
 * @property left the expression's left operand.
 * @property right the expression's right operand.
 */
data class ArrayExpression<T : Any>(
    val type: ArrayExpressionType,
    val left: ScalarExpression<TextArray>,
    val right: ScalarExpression<*>,
    override val sqlType: SqlType<T>,
    override val isLeafNode: Boolean = false,
    override val extraProperties: Map<String, Any> = emptyMap()
) : ScalarExpression<T>()


/**
 * Array contains operator, translated to the @> operator in PostgreSQL.
 */
fun ColumnDeclaring<TextArray>.contains(expr: ColumnDeclaring<TextArray>): ArrayExpression<Boolean> {
    return ArrayExpression(ArrayExpressionType.CONTAINS, asExpression(), expr.asExpression(), BooleanSqlType)
}

/**
 * Array contains operator, translated to the @> operator in PostgreSQL.
 */
fun ColumnDeclaring<TextArray>.contains(argument: TextArray): ArrayExpression<Boolean> {
    return this.contains(wrapArgument(argument))
}

/**
 * Array contained in operator, translated to the <@ operator in PostgreSQL.
 */
fun ColumnDeclaring<TextArray>.containedIn(expr: ColumnDeclaring<TextArray>): ArrayExpression<Boolean> {
    return ArrayExpression(ArrayExpressionType.CONTAINED_IN, asExpression(), expr.asExpression(), BooleanSqlType)
}

/**
 * Array contained in operator, translated to the <@ operator in PostgreSQL.
 */
fun ColumnDeclaring<TextArray>.containedIn(argument: TextArray): ArrayExpression<Boolean> {
    return this.containedIn(wrapArgument(argument))
}

/**
 * Array overlap operator, translated to the && operator in PostgreSQL.
 */
fun ColumnDeclaring<TextArray>.overlap(expr: ColumnDeclaring<TextArray>): ArrayExpression<Boolean> {
    return ArrayExpression(ArrayExpressionType.OVERLAP, asExpression(), expr.asExpression(), BooleanSqlType)
}

/**
 * Array overlap operator, translated to the && operator in PostgreSQL.
 */
fun ColumnDeclaring<TextArray>.overlap(argument: TextArray): ArrayExpression<Boolean> {
    return this.overlap(wrapArgument(argument))
}`