How to modify NEW record in trigger function

Question

I have two simple tables in postgres below: models table:

id	Model_name
1	l01
2	l06

overrides table:

id	value	model_id
1	26	1
2	54	2

with one to many relationship, where each model can many overrides.

I have setup a NOTIFY and trigger function on override table and I can LISTEN to the channel and get NEW records which new overrides inserted into the override table.

create function NotifyOnDataChange()
   returns trigger 
   language plpgsql
  as
$BODY$
DECLARE 
  data JSON;
  notification JSON;
BEGIN
  data = row_to_json(NEW);
  notification = json_build_object(
            'table',TG_TABLE_NAME,
            'action', TG_OP, 
            'data', data);  
            
  PERFORM pg_notify('datachange', notification::TEXT);
  RETURN NEW;
END;
$BODY$;

CREATE TRIGGER "OnDataChange"
  AFTER INSERT OR DELETE OR UPDATE 
  ON "public"."overrides"
  FOR EACH ROW
  EXECUTE PROCEDURE NotifyOnDataChange();

I am listening to the notifications in a .NET console application. but I do need to query DB again for model_name knowing the model_id from json object.

My problem is that I rather bring back model_name from postgres instead of model_id but not sure how I can achieve that.So, is there a way to join on Records and replace model_id with its value model_name from models table?

Answer 1

You can query models in the trigger function. Note also, that the value of the new record is undefined when the trigger is fired on DELETE, so you should check this.

create or replace function NotifyOnDataChange()
   returns trigger 
   language plpgsql
as
$body$
declare 
    data json;
    notification json;
    m_name text;
    rec record;
begin
    rec = case tg_op
        when 'DELETE' then old
        else new end;

    select model_name
    into m_name
    from models 
    where id = rec.id;
    
    data = row_to_json(rec);
    notification = json_build_object(
            'table',TG_TABLE_NAME,
            'action', TG_OP, 
            'data', data, 
            'model_name', m_name);
    perform pg_notify('datachange', notification::text);
    return rec;
end;
$body$;