在Postgres触发器中使用tsvector更新触发器

Question

我有一个tsvector列，我希望在行更改时更新它。对于一个INSERT，我使用以下触发器：

CREATE TRIGGER albums_vector_insert BEFORE INSERT
ON albums
FOR EACH ROW EXECUTE PROCEDURE
 tsvector_update_trigger('search_vector', 'pg_catalog.english', 'name')

这个看起来是可以正常工作的。我希望在UPDATE子句上使用另一个触发器，但我只想在名称实际更改时才触发它，这样我就不会浪费时间更新不必要的搜索向量。我尝试过这样：

CREATE TRIGGER albums_vector_update BEFORE UPDATE ON albums
FOR EACH ROW EXECUTE PROCEDURE
    IF NEW.name <> OLD.name THEN
        tsvector_update_trigger(search_vector, 'pg_catalog.english', name);
    END IF;

但是，当我尝试创建触发器时，它会抛出两个错误：

Error : ERROR:  syntax error at or near "NEW"
LINE 3:  IF NEW.name <> OLD.name THEN
            ^
Error : ERROR:  syntax error at or near "IF"
LINE 1: END IF
            ^

据我所知，如果我使用触发器过程语法，比如： CREATE OR REPLACE FUNCTION something() RETURNS TRIGGER 然后将我的函数与触发器关联，那么我将无法使用内置的tsvector_update_trigger函数，而需要自己处理ts_vector操作。因此我尝试使用一体化的触发器+过程语法...

有什么建议吗？

- Cody Caughlan

Answer 1

这是我最终得到的结果：

CREATE FUNCTION albums_vector_update() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        new.search_vector = to_tsvector('pg_catalog.english', COALESCE(NEW.name, ''));
    END IF;
    IF TG_OP = 'UPDATE' THEN
        IF NEW.name <> OLD.name THEN
            new.search_vector = to_tsvector('pg_catalog.english', COALESCE(NEW.name, ''));
        END IF;
    END IF;
    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON albums
FOR EACH ROW EXECUTE PROCEDURE albums_vector_update();

- Cody Caughlan

---

代码中有个错别字。你应该使用 BEFORE INSERT OR UPDATE 触发器，因为 AFTER 触发器会忽略 NEW。 - anon

Answer 2

一个替代方案可能是像“distinct from”这样的东西，它确实帮了我几次忙。虽然代码未经测试。

CREATE TRIGGER albums_vector_update 
BEFORE INSERT OR UPDATE ON albums
FOR EACH ROW 
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE PROCEDURE tsvector_update_trigger(search_vector, 'pg_catalog.english', NEW.name);

- mhenrixon

---

这个也帮了我大忙。我在使用“<>”运算符时遇到了麻烦，但是“DISTINCT FROM”与“<>”表达式有些不同，特别是当你将NEW.name与之前为null的OLD.name进行比较时。虽然我不是DBA，但在我的经验中，当OLD.name为null且NEW.name为“非null值”时，“NEW.name <> OLD.name”表达式返回false，但“NEW.name IS DISTINCT FROM OLD.name”返回true。 - u.unver34

Answer 3

当您插入行时，无需检查此条件，实际上您无法检查，因为没有旧记录，所以：

CREATE TRIGGER albums_vector_insert 
BEFORE INSERT ON albums
FOR EACH ROW 
EXECUTE PROCEDURE tsvector_update_trigger(search_vector, 'pg_catalog.english', name);

更新：

CREATE TRIGGER albums_vector_update 
BEFORE UPDATE ON albums
FOR EACH ROW 
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE PROCEDURE tsvector_update_trigger(search_vector, 'pg_catalog.english', name);

- Velerad

Answer 4

名称比较逻辑需要放入tsvector_update_trigger过程代码中，类似于：

IF TG_OP = 'UPDATE' THEN
    IF NEW.name <> OLD.name THEN
       -- Do tsvector update
    END IF;
END IF;

然后创建一个在INSERT和UPDATE之前被调用的触发器

- Gavin