postgresql - trigger function with update not working with initial insert

Question

I want to run a trigger each time a record is inserted or updated on table knowledgebase_messages which will vectorize the message column, using the joined pg_language_dictionary (e.g.: "French") for that message's locale_id.

Currently, the trigger working on row update, but not initial insert. Meaning, if I insert a row, then run a query to get records, updated the row in any way, or refresh the postgres UI, I see the vectorized value correctly there.

Trigger function:

  runMessageVectorTriggerFn = 'CREATE OR REPLACE FUNCTION run_message_vector() ' +
    'RETURNS TRIGGER ' +
    'AS $BODY$ ' +
      'BEGIN ' +
        'IF pg_trigger_depth() <> 1 THEN ' +
           'RETURN NEW; ' +
        'END IF; ' +
        'UPDATE knowledgebase_messages kbm ' +
        'SET vector = to_tsvector(lang.pg_dictionary_name::regconfig, kbm.message) ' +
        'FROM locales as loca ' +
        'JOIN languages as lang ON loca.language_id = lang.id ' +
        'WHERE kbm.locale_id = loca.id;' +
        'RETURN NEW; ' +
      'END; ' +
    '$BODY$ LANGUAGE plpgsql';

Trigger:

  runMessageVectorTrigger = 'CREATE TRIGGER run_message_vector AFTER INSERT OR UPDATE ON knowledgebase_messages ' +
    'FOR EACH ROW EXECUTE PROCEDURE run_message_vector()';

Daniel Vérité · Accepted Answer · 2017-02-03 16:54:37Z

1

When doing this in the BEFORE INSERT trigger, it doesn't update the row being inserted because the row doesn't exist yet:

UPDATE knowledgebase_messages kbm...

Besides, it's not clear what is the intention in your UPDATE because the WHERE clause seems to ignore the NEW row, as if it was implicit that it was the target.

Anyway, instead of that, to change the row being inserted/updated, do something along the lines of:

-- the SELECT must return 1 row, or in the worst case no row
-- otherwise an error will occur.
NEW.vector = (
  SELECT
    to_tsvector(lang.pg_dictionary_name::regconfig, NEW.message)
  FROM locales as loca JOIN languages as lang
    ON loca.language_id = lang.id 
  WHERE NEW.locale_id = loca.id
);

RETURN NEW;

both in BEFORE UPDATE and BEFORE INSERT cases.

Then you can remove this IF pg_trigger_depth() <> 1...

edited Feb 3, 2017 at 16:54

answered Feb 3, 2017 at 16:24

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3871 Over a year ago

So how should my query structure look within SELECT? I'm not sure how to restructure the above

Collectives™ on Stack Overflow

postgresql - trigger function with update not working with initial insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related