Using to_tsvector in ecto during insert

Hi all,
I hope you all are having a good Sunday. I am working on an application using Phoenix, Apollo, graphql and react. One of features in the app is text search. I am using postgresql ‘a full text search. It is working fine in the query to pull data. I want to use to_tsvector when inserting a new record in the table.

Currently I am updating the search tokens column using a database trigger. I want to avoid using database triggers.

Is there any example how to use to_tsvector in a changeset?

Appreciate help.

2 Likes

You could use generated columns in Postgres. I realize that’s not exactly what you are asking for but may be better than triggers.

2 Likes

Thank you @dbern,

I am looking for a way to populate a search_tokens field, which is a tsvector field in Postgresql. I am currently doing this using an update command as follows

UPDATE doctors SET search_tokens = to_tsvector(doctor_name || ’ ’ || full_address || ’ ’ || specialization)

The above creates the search_tokens field as follows

‘dr’:1 ‘physican’:3 ‘john’:2.

and I could then use the search_token field to perform a text search. For the search part, I am using fragment as follows

fragment(“search_tokens @@ plainto_tsquery(?)” and it is working great.

I want to find a way to update the search_token field using

What I am looking for specifically is an ability to update the search_tokens field using to_tsvector. Is there a way I can do that in ecto changeset?

Appreciate help.

As @dbern commented above you could use a generated column. The column would be updated automatically whenever the column(s) it depends on update.

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). PostgreSQL currently implements only stored generated columns.

More info in postgresql documentation