在UPDATE语句中Unnest行列表更新单链表记录顺序遇语法错误

问题场景 #

用Kotlin+jOOQ实现单链表结构记录的重排序，通过构建(previous, current) ID元组数组，在UPDATE语句的FROM子句中执行unnest操作批量更新previous_composition_element_id字段，但执行时触发PostgreSQL语法错误。

错误信息 #

jOOQ; bad SQL grammar [
    update "public"."composition_element"
    set
        "previous_composition_element_id" = previous_element_id,
        "modified_by_id" = ? 
    from
        unnest(cast(? as any[])) as "values" (previous_element_id, current_element_id)
    where "public"."composition_element"."id" = current_element_id
];

nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "any"

错误原因 #

PostgreSQL不支持any[]作为数组类型，jOOQ自动推断数组类型时生成了不合法的类型声明，导致语法报错。

修复方案 #

需要显式指定unnest操作的数组元素类型，避免jOOQ生成any[]这种无效类型。以下提供两种可行的修改方式：

方式一：显式指定unnest的元素类型 #

修改from子句中的unnest调用，通过重载方法传入明确的SQL数据类型：

private fun updateCompositionElementOrdering(elementIdsInExpectedOrder: List<Int>) {
    val PREVIOUS_ELEMENT = DSL.field("previous_element_id", Int::class.javaObjectType)
    val CURRENT_ELEMENT = DSL.field("current_element_id", Int::class.javaObjectType)

    val previousAndCurrent = elementIdsInExpectedOrder.mapIndexed { i, currentElement ->
        val previousElementId = if (i == 0) null else elementIdsInExpectedOrder[i - 1]
        DSL.row(previousElementId, currentElement)
    }

    ctx.update(COMPOSITION_ELEMENT)
        .set(COMPOSITION_ELEMENT.PREVIOUS_COMPOSITION_ELEMENT_ID, PREVIOUS_ELEMENT)
        .from(
            // 显式指定元组的两个字段类型：可为空的整数、整数
            DSL.unnest(previousAndCurrent, SQLDataType.INTEGER.nullable(), SQLDataType.INTEGER)
                .`as`(DSL.name("p"), PREVIOUS_ELEMENT.unqualifiedName, CURRENT_ELEMENT.unqualifiedName)
        )
        .where(COMPOSITION_ELEMENT.ID.eq(CURRENT_ELEMENT))
        .execute()
}

方式二：用VALUES子句替代unnest #

如果觉得unnest的类型处理麻烦，也可以改用VALUES子句实现批量更新，语法更直观：

private fun updateCompositionElementOrdering(elementIdsInExpectedOrder: List<Int>) {
    val PREVIOUS_ELEMENT = DSL.field("previous_element_id", Int::class.javaObjectType)
    val CURRENT_ELEMENT = DSL.field("current_element_id", Int::class.javaObjectType)

    // 构建VALUES子句的行数据
    val valueRows = elementIdsInExpectedOrder.mapIndexed { i, currentElement ->
        val previousElementId = if (i == 0) null else elementIdsInExpectedOrder[i - 1]
        DSL.values(previousElementId, currentElement)
    }

    ctx.update(COMPOSITION_ELEMENT)
        .set(COMPOSITION_ELEMENT.PREVIOUS_COMPOSITION_ELEMENT_ID, PREVIOUS_ELEMENT)
        .from(
            DSL.select(PREVIOUS_ELEMENT, CURRENT_ELEMENT)
                .from(DSL.values(valueRows).`as`("p", PREVIOUS_ELEMENT.name, CURRENT_ELEMENT.name))
        )
        .where(COMPOSITION_ELEMENT.ID.eq(CURRENT_ELEMENT))
        .execute()
}

验证结果 #

修改后jOOQ会生成PostgreSQL可解析的SQL，例如方式一生成的SQL类似：

update "public"."composition_element"
set "previous_composition_element_id" = previous_element_id
from unnest(cast(? as record2(int4, int4)[])) as "p" (previous_element_id, current_element_id)
where "public"."composition_element"."id" = current_element_id

内容的提问来源于stack exchange，提问作者User1291