1

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

I found that changing the table trigger to a BEFORE trigger allowed the function to work as desired. While I am glad that it works and now understand that NEW is only valid in a BEFORE trigger, I am left with a question: what is the correct way to refer to the inserted or updated row in an AFTER trigger?

  • 1
    new and old are the correct way to reference them in any trigger (including an AFTER trigger). However you can not make any changes to those values because - well - it's after those changes have been persisted. Assigning values does work - for the duration of the trigger, but those modifications are not stored in the table – a_horse_with_no_name Sep 10 '16 at 7:30
  • Having thought about this, and reading your comment, I understand that the references are valid but (as you wrote) the operation has already completed. Any further changes required will need another statement of some kind. – Mophilly Sep 11 '16 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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