如何在 PostgreSQL 中将 tsvector_update_trigger 与数组类型一起使用? [英] How to use tsvector_update_trigger with array types in PostgreSQL?

问题描述

我正在使用 pg_search gem 将全文搜索添加到我应用中的表格中.

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

我的问题是在尝试创建触发器以在任何更改后使 tsv 列保持最新时，这是我的迁移:

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

在这种情况下，related_intelligences 列是一个数组，因此在尝试查询时出现以下错误:

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

如何在此 tsv 向量列中包含数组?

How can I include arrays within this tsv vector column?

推荐答案

我是这样做的:

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

tsvector_update_trigger 不能用于数组列，而是我根据 postgresql 搜索功能页面.

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.

我使用 array_to_string 将数组元素转换为文本.

I used array_to_string to convert the array elements to text.

这篇关于如何在 PostgreSQL 中将 tsvector_update_trigger 与数组类型一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！