up vote 1 down vote favorite

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?

share|improve this question

2 Answers

up vote 3 down vote accepted

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 AFTER INSERT OR UPDATE ON albums
FOR EACH ROW EXECUTE PROCEDURE albums_vector_update();
share|improve this answer
up vote 0 down vote

The name comparison logic needs to go into the tsvector_update_trigger procedure code. something like:

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

Then create a trigger that is called before INSERT and UPDATE

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.