Adding more columns to Trigger

[CarlosUvaSilva]

Hello.

I'm using pg_search 2.0.1 and a long time ago I did:

#Migration

def up
    add_column :users, :tsv, :tsvector
    add_index :users, :tsv, using: "gin"

    execute <<-SQL
    CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
    ON users FOR EACH ROW EXECUTE PROCEDURE
    tsvector_update_trigger(
      tsv, 'pg_catalog.simple', name, first_name, last_name, bio
    );
    SQL

    now = Time.current.to_s(:db)
    update("UPDATE players SET updated_at = '#{now}'")
  end

#User model

pg_search_scope :search_for, against: [:name, :first_name, :last_name, :bio],
  using: {
    tsearch: {
      tsvector_column: 'tsv',
      prefix:  true,
      dictionary: 'simple',
      any_word: true
    }
  }

Now I need to add a new column to the search vector. I suppose I need a new migration but I'm not sure what syntax to use.

Thank you

[arcreative]

I've created a class that inherits from ActiveRecord::Migration[5.0] that has methods to easily remove and re-add triggers and update functions, it's proven invaluable when updating indexed columns or fulltext weighting.

[arcreative]

Implementation looks like the following:

class SwapTsvUpdateTriggerForUsersV2 < SearchTsvectorMigration
  def change
    reversible do |dir|
      dir.up do
        remove_trigger_and_index(User)
        add_users_trigger_and_index_v2
      end
      dir.down do
        remove_trigger_and_index(User)
        add_users_trigger_and_index_v1
      end
    end

    say_with_time 'Executing triggers for users model' do
      update('UPDATE users SET updated_at = CURRENT_TIMESTAMP')
    end
  end
end

where removal method looks like the following:

  def remove_trigger_and_index(model)
    model_name = model.table_name

    say_with_time "Dropping trigger for #{model_name} model" do
      execute <<-SQL
          DROP TRIGGER #{trigger_name}
          ON #{model_name}
      SQL
    end

    remove_index model_name.to_sym, :tsv
    remove_column model_name.to_sym, :tsv
  end

and adding looks much like what you have posted above.

[CarlosUvaSilva]

hello @arcreative thank you for the feedback. you have a repo with a "full" example on how to run this? thanks

[arcreative]

Unfortunately I don’t (all proprietary), but I can post a more complete class for you in a bit.