Postgresql – assignment in trigger function does not work

postgresqltrigger

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

Best Answer

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?

Related Question