如何为非字符类型数据创建tsvector_update_trigger触发器

报错原因 #

PostgreSQL 内置的tsvector_update_trigger仅支持传入字符类型字段生成tsvector，整数、日期、布尔等非字符类型字段会触发类型不匹配错误。

实现步骤 #

1. 编写自定义触发器函数 #

自定义函数内将非字符类型字段显式转为text类型后，再调用to_tsvector生成索引字段：

CREATE OR REPLACE FUNCTION custom_doc_id_tsv_update()
RETURNS TRIGGER AS $$
BEGIN
  NEW.tsv_doc_id := to_tsvector('pg_catalog.english', NEW.doc_id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

2. 绑定触发器到目标表 #

CREATE TRIGGER tr_doc_id_col
BEFORE INSERT OR UPDATE OF doc_id
ON document
FOR EACH ROW
EXECUTE FUNCTION custom_doc_id_tsv_update();

优化说明 #

• 触发器指定UPDATE OF doc_id条件，仅当doc_id字段发生变更时才触发tsvector重计算，避免无关字段更新时的性能损耗
• 删除操作无需额外处理，行删除时对应的tsv_doc_id字段会随整行数据一同清除
• 若需要合并多字段生成tsvector，可在函数内拼接转换后的多字段值，示例：

  NEW.tsv_combined := to_tsvector('pg_catalog.english', 
    COALESCE(NEW.title::text, '') || ' ' || COALESCE(NEW.doc_id::text, '') || ' ' || COALESCE(NEW.create_time::text, '')
  );
  

内容的提问来源于stack exchange，提问作者Adnan Javed