PostgreSQL插入行时文本搜索列触发器函数失效问题排查

插入新记录时，BEFORE INSERT触发器执行阶段，新记录还未写入prod.idea表，此时SELECT ... FROM prod.idea WHERE idea.id = NEW.id查询不到任何数据，导致text_search变量为空，最终NEW.text_search被设为空值。而更新操作时，记录已经存在，查询能返回数据，因此正常工作。

直接使用触发器中的NEW对象访问当前要插入/更新的字段，不需要从表中查询，这样无论是插入还是更新都能正确计算tsvector值。修改后的触发器函数如下：

CREATE OR REPLACE FUNCTION prod.update_idea_text_search()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$ 
BEGIN
   NEW.text_search := 
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.title, '')), 'A') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.subtitle, '')), 'A') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.strategy, '')), 'A') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.summary, '')), 'B') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.conditions, '')), 'B') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.strategy_data::text, '')), 'B') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.tags::text, '')), 'A') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.id::text, '')), 'B') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.buy::text, '')), 'C') ||
       setweight(to_tsvector('english'::regconfig, COALESCE(NEW.sell::text, '')), 'C');
                                                                                                    
   RETURN NEW;
END
$BODY$;

• 去掉不必要的''::text显式类型转换，PostgreSQL会自动处理字符串常量与text类型的兼容。
• 无需声明额外的text_search变量，直接赋值给NEW.text_search更简洁。
• 当所有字段都为空时，表达式本身会生成空tsvector，因此可以省略COALESCE（如果text_search字段允许为空的话）。

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