For the table, an AFTER trigger is supposed to update the tsvector column on insert and update. The assignment works outside the function using an update statement. Also, the notice in the BEGIN block appears in the log as desired. However, the assignment is not made when the trigger fires. Columns in the update or insert are updated; only the fts_vector column is empty.
Logged on user is part of a group that has execute privileges on the function and insert/update privileges on the table.
What might be preventing this from working? All answers and suggestions are welcome.
CREATE TABLE customer ( name character varying(1000) NOT NULL, address character varying(1000), customer_uid serial NOT NULL, fts_vector tsvector, CONSTRAINT customer_pkey PRIMARY KEY (customer_uid); CREATE OR REPLACE FUNCTION vector_update_customer() RETURNS trigger AS $BODY$ BEGIN RAISE NOTICE 'vector_update_customer invoked'; NEW.fts_vector = to_tsvector('english', coalesce(NEW.name,'') || ' ' || coalesce(NEW.address,'')); RETURN NEW; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'vector_update_customer failed'; END $BODY$ LANGUAGE plpgsql VOLATILE CREATE TRIGGER trigger_vector_update_customer AFTER INSERT OR UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE vector_update_customer();