How to use tsvector_update_trigger with array types in PostgreSQL?

Question

I'm using the pg_search gem to add full text search to a table in my app.

My problem is when trying to create a trigger to keep the tsv column up to date after any change, this is my migration:

class AddStoreItemsIndex < ActiveRecord::Migration[5.0]
  def up
    add_column :store_items, :tsv, :tsvector
    add_index :store_items, :tsv, using: "gin"

    execute <<-SQL
      CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
      ON store_items FOR EACH ROW EXECUTE PROCEDURE
      tsvector_update_trigger(
        tsv, 'pg_catalog.english', title, full_description, recommended_age, related_intelligences
      );
    SQL
  end

  ...
end

In this case, the related_intelligences column is an Array, so when trying to query this I get the following error:

ActiveRecord::StatementInvalid:
       PG::DatatypeMismatch: ERROR:  column "related_intelligences" is not of a character type

How can I include arrays within this tsv vector column?

Answer 1

This is how I did it:

class AddStoreItemsIndex < ActiveRecord::Migration[5.0]
  def up
    add_column :store_items, :tsv, :tsvector
    add_index :store_items, :tsv, using: 'gin'

    execute <<-SQL
      CREATE FUNCTION update_tsv() RETURNS trigger AS $$
      BEGIN
        new.tsv :=
          to_tsvector('pg_catalog.english', coalesce(new.title,'')) ||
          to_tsvector('pg_catalog.english', coalesce(new.full_description,'')) ||
          to_tsvector('pg_catalog.english', coalesce(new.recommended_age,'')) ||
          to_tsvector('pg_catalog.english', coalesce(array_to_string(new.related_intelligences, ' '),''));
        return new;
      END
      $$ LANGUAGE plpgsql;

      CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
      ON store_items FOR EACH ROW EXECUTE PROCEDURE update_tsv();
    SQL
  end

  def down
    execute <<-SQL
      DROP TRIGGER tsvectorupdate
      ON store_items;
      DROP FUNCTION update_tsv();
    SQL

    remove_index :store_items, :tsv
    remove_column :store_items, :tsv
  end
end

The tsvector_update_trigger can't be used for array columns, instead I created a custom function based on the code sample from the postgresql search features page.

I used array_to_string to convert the array elements to text.