A modern programming language that makes developers happier.

kotlinlang

hi everyone, i'm working on implementing a search endpoint in my ktor backend using exposed with PostgreSQL. For that, I want to use FTS and have already created the ts_vector columns in the database itself. Now, I need to tie it with my exposed table definitions and entities and use it to filter based on the user's query.

I have not yet found any relevant posts/articles online on this, does anyone have any suggestions? My current S.O. post is at <https://stackoverflow.com/questions/79010574/defining-and-using-postgresqls-ts-vector-column-type-in-ktor-using-exposed>. I have also recently found this github issue <https://github.com/JetBrains/Exposed/issues/64> but I'm not sure if the solution works for my case since I don't want to convert another column to a ts_vector.

Any help is greatly appreciated!

Figured out a solution if anyone else runs into this issue. I am going to write an article on it and try to also create a library so that others can use it in their projects and hopefully help improve it. I will attach the links here when that happens.

Hi <@U05RY7FDBJ6> In case you still need it, I extended base type `StringColumnType` to reduce some boilerplate:
```class TsVectorColumnType : StringColumnType() {
    override fun sqlType(): String = "tsvector"

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val parameterValue: PGobject? = value?.let {
            PGobject().apply {
                type = sqlType()
                this.value = value as? String
            }
        }
        super.setParameter(stmt, index, parameterValue)
    }
}```
`registerColumn()` would need to be invoked in the table definition to actually map to the created column in the database:
```fun Table.tsVector(name: String): Column&lt;String&gt; = registerColumn(name, TsVectorColumnType())```
Please see <http://jetbrains.github.io/Exposed/data-types.html#custom-data-types|docs> for more examples of how to implement and use custom data types.

&gt; I'm having trouble understanding how the logic would work for using it to search based on the user's query.
I'm not sure what you mean. Do you have an example of the exact SQL query that you need to generate?

For a simple example, you could use the custom functions from the linked GH issue to create a query like this:
```object StarWarsFilms : Table("star_wars_films") {
    val title = varchar("title", 128)
    val titleTS = tsVector("title_ts")
}

// inserts...
// I'm not sure if you are using a trigger to insert into the tsvector column.
// For simplicity I used an insert that just calls toTSVector() on the title string

StarWarsFilms
    .select(StarWarsFilms.title)
    .where { StarWarsFilms.titleTS tsMatches toTSQuery("Star") }
    .toList()
// SELECT star_wars_films.title
// FROM star_wars_films
// WHERE star_wars_films.title_ts @@ to_tsquery('Star')```
