postgresqltriggersnotifylisten

Postgres notify: notify with row id on row create/delete/update in table


I want to create LISTE/NOTIFY pipeline with trigger_function that sent NOTIFY.

In my NOTIFY i want to get message with row id for any create/delete/update with row in table.

How can i write such notify trigger_function ?

So far I have next migration? witch create trigger without row id

CREATE OR REPLACE FUNCTION notify_my_table_update() RETURNS TRIGGER AS $$
    BEGIN
    PERFORM pg_notify('my_table_update','');
    RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_my_table_update ON my_table;
CREATE TRIGGER trigger_my_table_update
  AFTER UPDATE OR DELETE OR INSERT OR TRUNCATE
  ON my_table
  EXECUTE PROCEDURE notify_my_table_update();

Solution

  • Steps are mentioned below:

    1. Create the table my_table
    CREATE TABLE my_table(
    id int,
    data varchar
    )
    
    1. Then Write Trigger Procedure:
    CREATE OR REPLACE FUNCTION notify_my_table_update() RETURNS TRIGGER AS $$
        DECLARE
        row RECORD;
        output TEXT;
        
        BEGIN
        -- Checking the Operation Type
        IF (TG_OP = 'DELETE') THEN
          row = OLD;
        ELSE
          row = NEW;
        END IF;
        
        -- Forming the Output as notification. You can choose you own notification.
        output = 'OPERATION = ' || TG_OP || ' and ID = ' || row.id;
        
        -- Calling the pg_notify for my_table_update event with output as payload
    
        PERFORM pg_notify('my_table_update',output);
        
        -- Returning null because it is an after trigger.
        RETURN NULL;
        END;
    $$ LANGUAGE plpgsql;
    
    1. Creating an after trigger on table my_table for INSERT/UPDATE/DELETE
    CREATE TRIGGER trigger_my_table_update
      AFTER INSERT OR UPDATE OR DELETE
      ON my_table
      FOR EACH ROW
      EXECUTE PROCEDURE notify_my_table_update();
      -- We can not use TRUNCATE event in this trigger because it is not supported in case of FOR EACH ROW Trigger 
    
    1. Registering the my_table_update channel to receive the notification.
    LISTEN my_table_update;
    
    1. Now you can recieve the notification on your PSQL prompt within session.

    INSERT OPERATION

    TEST=# INSERT into my_table VALUES (1, 'TESTING');
    INSERT 0 1
    Asynchronous notification "my_table_update" with payload "OPERATION = INSERT and ID = 1" received from server process with PID 9057.
    
    

    UPDATE OPERATION

    TEST=# update my_table  set data='NOTIFY' where ID>=2;
    UPDATE 2
    Asynchronous notification "my_table_update" with payload "OPERATION = UPDATE and ID = 2" received from server process with PID 9057.
    Asynchronous notification "my_table_update" with payload "OPERATION = UPDATE and ID = 3" received from server process with PID 9057.
    

    DELETE OPERATION

    TEST=# delete from my_table ;
    DELETE 3
    Asynchronous notification "my_table_update" with payload "OPERATION = DELETE and ID = 1" received from server process with PID 9057.
    Asynchronous notification "my_table_update" with payload "OPERATION = DELETE and ID = 2" received from server process with PID 9057.
    Asynchronous notification "my_table_update" with payload "OPERATION = DELETE and ID = 3" received from server process with PID 9057.