pg_notify metodu - LISTEN ve NOTIFY

Giriş

Açıklaması şöyle

The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

Açıklaması şöyle

PostgreSQL has got the LISTEN/NOTIFY commands. Set up a trigger on each table that calls [NOTIFY ‘tableName_changed’]. Any process connected to the database can wait using [LISTEN ‘tableName_changed’].

Örnek

Şöyle yaparız. table_name yerine istediğimiz tablo ismi gelir. Burada kendi trigger metodumuzu belirtiyoruz.

CREATE TRIGGER table_change 
    AFTER INSERT OR UPDATE OR DELETE ON table_name
    FOR EACH ROW EXECUTE PROCEDURE notify_change();

Trigger metodumuz şöyler. pg_notify() çağrısı yapılıyor. Burada değişen tablo ismi TG_TABLE_NAME ile elde edilir.

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
    BEGIN
        SELECT pg_notify('test', TG_TABLE_NAME);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Örnek

Şöyle yaparız

CREATE TABLE PUBLIC.TBLEXAMPLE
(
  KEY1 CHARACTER VARYING(10) NOT NULL,
  KEY2 CHARACTER VARYING(14) NOT NULL,
  VALUE1 CHARACTER VARYING(20),
  VALUE2 CHARACTER VARYING(20) NOT NULL,
  CONSTRAINT TBLEXAMPLE_PKEY PRIMARY KEY (KEY1, KEY2)
);

CREATE OR REPLACE FUNCTION PUBLIC.NOTIFY() RETURNS TRIGGER AS
$BODY$
BEGIN
  PERFORM pg_notify('myevent', row_to_json(NEW)::text);
  RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;


CREATE TRIGGER TBLEXAMPLE_AFTER
 AFTER insert or update or delete ON PUBLIC.TBLEXAMPLE
FOR EACH ROW
EXECUTE PROCEDURE PUBLIC.NOTIFY();