Using tsvector update trigger in Postgres trigger

Question

I have a tsvector column that I want to update when the row changes. For an INSERT I am using this trigger:

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

Which works fine, seemingly. I would like to use another trigger on UPDATE clauses, but I only want it to fire when the name actually changes, so I dont waste cycles updating the search vector needlessly. I've tried this:

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;

But this throws 2 errors when I try to create the trigger:

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
            ^

From my understanding, if I use the trigger procedure syntax, ala:

CREATE OR REPLACE FUNCTION something() RETURNS TRIGGER

then associate my function with a trigger, then I wont be able to use the built-in tsvector_update_trigger function and will need to handle the ts_vector manipulation myself. Hence my trying to use the all-in-one-trigger+procedure syntax...

Any ideas?

Answer 1

This is what I ended up with:

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();

Answer 2

An alternative could be something like distinct from, it really helped me a few times. Untested code though.

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);

Answer 3

When you insert row you don't have to check this condition, actually you can't as there is no OLD record so:

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

For update:

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);