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();
Steps are mentioned below:
my_table
CREATE TABLE my_table(
id int,
data varchar
)
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;
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
my_table_update
channel to receive the notification.LISTEN my_table_update;
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.