Using to_tsvector in ecto during insert

annrap1d · September 12, 2021, 6:09pm

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.

dbern · September 14, 2021, 11:23am

github.com

elixirstream/elixirstream.dev/blob/main/priv/repo/migrations/20210301012824_add_tips.exs

defmodule ElixirStream.Repo.Migrations.AddTips do
  use Ecto.Migration
  @disable_ddl_transaction true
  @disable_migration_lock true

  def change do
    execute(
      "CREATE EXTENSION IF NOT EXISTS \"pg_trgm\"",
      "DROP EXTENSION IF EXISTS \"pg_trgm\""
    )

    create table("tips", primary_key: false) do
      add :id, :binary_id, primary_key: true, default: fragment("gen_random_uuid()")

      add :title, :text, null: false
      add :description, :text, null: false
      add :code, :text, null: false
      add :code_image_url, :string
      add :published_at, :utc_datetime
      add :approved, :boolean, null: false, default: false

This file has been truncated. show original

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

annrap1d · September 20, 2021, 12:14am

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.

ambrosemungai · September 20, 2021, 4:40pm

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.