TSVector触发器及to_tsvector更新中整数列转TEXT的实现问题

• 内置的tsvector_update_trigger仅支持传入字符串类型列作为参数，不会自动做类型转换，直接传入数值类型的id列必然触发类型不匹配错误
• 你提供的UPDATE语句未显式对id列做类型转换就直接拼接，也会触发同类错误

1. 修正全量更新语句 #

显式将id列转为TEXT类型后再拼接，同时加入COALESCE避免空字段导致整个拼接结果为NULL：

UPDATE "customer" 
SET "CustomerSearchVector" = to_tsvector('german', 
  COALESCE("id"::TEXT, '') || ' ' || 
  COALESCE("firstName", '') || ' ' || 
  COALESCE("lastName", '') || ' ' || 
  COALESCE("company", '') || ' ' || 
  COALESCE("birthName", '')
);

2. 替换内置触发器为自定义触发器 #

内置触发器不支持自定义类型转换，需要自行编写触发器函数实现逻辑：

第一步：创建自定义触发器函数 #

CREATE OR REPLACE FUNCTION customer_search_vector_trigger()
RETURNS trigger AS $$
BEGIN
  NEW."CustomerSearchVector" = to_tsvector('pg_catalog.german',
    COALESCE(NEW."id"::TEXT, '') || ' ' ||
    COALESCE(NEW."firstName", '') || ' ' ||
    COALESCE(NEW."lastName", '') || ' ' ||
    COALESCE(NEW."company", '') || ' ' ||
    COALESCE(NEW."birthName", '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：替换原有触发器 #

-- 删掉之前创建的错误触发器
DROP TRIGGER IF EXISTS np_vector_update ON "customer";
-- 绑定新的自定义触发器
CREATE TRIGGER np_vector_update 
BEFORE INSERT OR UPDATE ON "customer" 
FOR EACH ROW 
EXECUTE PROCEDURE customer_search_vector_trigger();

可选优化：提升ID匹配结果优先级 #

如果希望搜索客户编号时，ID完全匹配的结果排序更靠前，可以给ID字段设置更高的检索权重：

CREATE OR REPLACE FUNCTION customer_search_vector_trigger()
RETURNS trigger AS $$
BEGIN
  NEW."CustomerSearchVector" = 
    -- ID部分设置最高的A权重
    setweight(to_tsvector('pg_catalog.german', COALESCE(NEW."id"::TEXT, '')), 'A') ||
    -- 其他字段设置次一级的B权重
    setweight(to_tsvector('pg_catalog.german', COALESCE(NEW."firstName",'') || ' ' || COALESCE(NEW."lastName",'') || ' ' || COALESCE(NEW."company",'') || ' ' || COALESCE(NEW."birthName",'')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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