postgresql - 触发器在插入/更新时不更新 tsvector

Question

我正尝试从几个表中的几个列中提取数据，以使其可进行全文搜索，但我的运气并不好。

这是我的功能:

CREATE OR REPLACE FUNCTION on_customer_save_udpate_tsv() RETURNS trigger AS $$ 
declare 
   tsv_text text; 
begin 
SELECT string_agg(cust_text.text, ' ') as agg_text into tsv_text 
FROM (SELECT concat("name", ' ', "phone") as text 
   FROM "Customers" 
   where "id" = NEW.id 
   UNION 
   SELECT concat("firstName", ' ', "lastName", ' ', "phone", ' ', "email") as text 
   FROM "Contacts" 
   where "customerId" = NEW.id 
   UNION 
   SELECT concat("streetLine1", ' ', "city", ' ', "state", ' ', "zip") as text 
   FROM "Addresses" 
   where "customerId" = NEW.id) cust_text; 
NEW.tsv := to_tsvector(coalesce(tsv_text,'')); 
return NEW; 
end 
$$ LANGUAGE plpgsql; 

这是我的触发器:

CREATE TRIGGER cust_tsv_trigger BEFORE INSERT OR UPDATE 
ON "Customers" FOR EACH ROW EXECUTE PROCEDURE on_customer_save_udpate_tsv();

但是，“Customers”.tsv 列在插入/更新后为空。

当我手动运行大部分函数时，它按预期工作。

DO $$ 
declare 
   tsv_text text; 
begin 
SELECT string_agg(cust_text.text, ' ') as agg_text into tsv_text 
FROM (SELECT concat("name", ' ', "phone") as text 
   FROM "Customers" 
   where "id" = 17 
   UNION 
   SELECT concat("firstName", ' ', "lastName", ' ', "phone", ' ', "email") as text 
   FROM "Contacts" 
   where "customerId" = 17
   UNION 
   SELECT concat("streetLine1", ' ', "city", ' ', "state", ' ', "zip") as text 
   FROM "Addresses" 
   where "customerId" = 17) cust_text; 

   UPDATE "Customers"
   SET tsv = to_tsvector(coalesce(tsv_text, ''))
   WHERE "id" = 17;
end 
$$ LANGUAGE plpgsql; 

我是 postgres 的新手。我错过了什么？

Answer 1

由于触发器在 BEFORE INSERT 或 UPDATE 运行时，让它从表中选择以获取新值是有缺陷的，因为新值还不存在。

考虑这部分查询:

SELECT concat("name", ' ', "phone") as text 
   FROM "Customers" 
   where "id" = NEW.id 

在 INSERT 的情况下，您将始终在此处获得 NULL，而在 UPDATE 的情况下，您将获得即将被替换的值而不是新值。

应该用作上述代码的直接替代的是:

SELECT (NEW.name || ' ' || NEW.phone) AS text

---

另一点看起来可疑的是:如果 3 个表中任何一个的用户详细信息中的任何字段为 NULL，那么整个连接的内容将是空的，因为代码应用了 coalesce 在最后而不是在每个单独的字段上都这样做。