skip to content
Alvin Lucillo

PostgreSQL notify channel

/ 1 min read

database, postgresql

💻 Tech

In PostgreSQL, you can create a channel to notify a listener when a row is inserted or updated like so:

CREATE OR REPLACE FUNCTION notify_request_insert_or_update
() RETURNS trigger AS 
$$
BEGIN
	PERFORM pg_notify ( 'request_channel' , NEW.id ::text ) ;
	RETURN NEW;
END;
$$
LANGUAGE
plpgsql; 

CREATE TRIGGER insert_or_update_request_trigger AFTER
INSERT OR UPDATE
    ON request FOR EACH ROW
EXECUTE FUNCTION notify_request_insert_or_update ();

Each time there’s an insert or update, a trigger is created, which calls the notify_request_insert_or_update function. Subsequently, the function will notify the channel.