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

Question

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

Antibes

Unable to understand the meaning of So far I have next migration? witch create trigger without row id – Cobbett

Answer 1

Steps are mentioned below:

1. Create the table my_table

CREATE TABLE my_table(
id int,
data varchar
)

2. 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;

3. 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 

4. Registering the my_table_update channel to receive the notification.

LISTEN my_table_update;

5. 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.

Cobbett