在Ktor中借助Exposed定义与使用PostgreSQL的ts_vector列类型

一、自定义TsVectorColumnType的问题与修正 #

你的自定义类思路方向是对的，但存在两处关键问题需要修正：

1. valueToDB和notNullValueToDB不应返回PGobject.value，而要直接返回PGobject实例——JDBC驱动需要识别这个对象的类型为tsvector，否则会被当作普通字符串处理，引发类型不匹配错误。
2. nonNullValueToString直接拼接单引号存在SQL注入风险，且不符合Exposed参数化查询的规范，应改为使用占位符让Exposed自动处理参数绑定。

修正后的TsVectorColumnType代码如下：

private const val TS_VECTOR_SQL_TYPE = "tsvector"

class TsVectorColumnType : ColumnType<String>() {
    override fun sqlType(): String = TS_VECTOR_SQL_TYPE

    override fun valueFromDB(value: Any): String? {
        return when (value) {
            is PGobject -> value.value
            is String -> value
            else -> null
        }
    }

    override fun valueToDB(value: String?): Any? {
        return value?.let {
            PGobject().apply {
                type = TS_VECTOR_SQL_TYPE
                this.value = it
            }
        }
    }

    override fun notNullValueToDB(value: String): Any {
        return PGobject().apply {
            type = TS_VECTOR_SQL_TYPE
            this.value = value
        }
    }

    override fun nonNullValueToString(value: String): String {
        return "?" // 使用占位符，交由Exposed处理参数化
    }
}

在表定义中，需通过registerColumn来注册该列（而非直接赋值）：

object YourTable : Table("your_table_name") {
    // 其他列定义...
    val tsvectorContent = registerColumn<String>("tsvector_content", TsVectorColumnType())
}

二、全文检索逻辑实现 #

PostgreSQL全文检索的核心是tsvector @@ tsquery的匹配逻辑，你可以通过Exposed的CustomFunction构造对应的SQL表达式来实现。

1. 基础检索实现 #

假设用户输入查询关键词query，使用plainto_tsquery（自动将空格解析为逻辑或）构造查询条件：

fun searchByQuery(query: String): List<YourEntity> {
    // 构造tsquery表达式：plainto_tsquery('english', ?)
    val tsQuery = CustomFunction<Any>(
        "plainto_tsquery",
        TsVectorColumnType(),
        stringLiteral("english"), // 指定文本搜索配置，中文可使用'pg_catalog.simple'或自定义配置
        stringParam(query)
    )

    // 构造匹配条件：tsvector_content @@ tsquery
    val matchCondition = CustomFunction<Boolean>(
        "@@",
        BooleanColumnType(),
        YourTable.tsvectorContent,
        tsQuery
    )

    // 执行查询并映射为实体
    return YourTable.select(matchCondition)
        .map { YourEntity.fromRow(it) }
}

2. 优化：定义扩展函数简化语法 #

可以给Column<String>定义扩展函数，让检索代码更简洁：

infix fun Column<String>.matches(query: String): Op<Boolean> {
    val tsQuery = CustomFunction<Any>(
        "plainto_tsquery",
        TsVectorColumnType(),
        stringLiteral("english"),
        stringParam(query)
    )
    return CustomFunction("@@", BooleanColumnType(), this, tsQuery)
}

// 使用时直接调用
fun searchSimplified(query: String): List<YourEntity> {
    return YourTable.select(YourTable.tsvectorContent matches query)
        .map { YourEntity.fromRow(it) }
}

3. 维护tsvector列的内容 #

为保证tsvector列与源文本同步，推荐两种方式：

• 数据库触发器自动更新：在PostgreSQL中创建触发器，当源字段（如title、content）更新时自动调用to_tsvector更新tsvector列（推荐方案，无需在代码层处理同步逻辑）。
• 代码层手动生成：插入/更新数据时，通过to_tsvector生成对应值：

// 插入示例
YourTable.insert {
    it[title] = "示例标题"
    it[content] = "示例内容"
    // 生成tsvector值
    it[tsvectorContent] = CustomFunction(
        "to_tsvector",
        TsVectorColumnType(),
        stringLiteral("english"),
        Op.build { title + stringLiteral(" ") + content }
    )
}

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