script.sql

DROP TRIGGER IF EXISTS TBLEXAMPLE_AFTER_STATEMENT ON TBLEXAMPLE;
DROP TRIGGER IF EXISTS TBLEXAMPLE_BEFORE_TRUNCATE ON TBLEXAMPLE;
DROP FUNCTION IF EXISTS NOTIFY();
DROP TABLE IF EXISTS PUBLIC.TBLEXAMPLE;

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 NOTIFY() RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF(TG_OP = 'DELETE') THEN
        PERFORM PG_NOTIFY('myEvent', '{"data": ' || ROW_TO_JSON(OLD)::TEXT || ', "type": "'::TEXT || TG_OP::TEXT|| '", "schemaName":"' ||  TG_TABLE_SCHEMA || '", "tableName":"' ||  TG_TABLE_NAME || '"}');
        RETURN OLD;
    ELSEIF(TG_OP = 'TRUNCATE') THEN
        PERFORM PG_NOTIFY('myEvent', '{"data": null, "type": "'::TEXT || TG_OP::TEXT|| '", "schemaName":"' ||  TG_TABLE_SCHEMA || '", "tableName":"' ||  TG_TABLE_NAME || '"}');
        RETURN NEW;
    ELSE
        PERFORM PG_NOTIFY('myEvent', '{"data": ' || ROW_TO_JSON(NEW)::TEXT || ', "type": "'::TEXT || TG_OP::TEXT|| '", "schemaName":"' ||  TG_TABLE_SCHEMA || '", "tableName":"' ||  TG_TABLE_NAME || '"}');
        RETURN NEW;
    END IF;
END;
$$;

ALTER FUNCTION NOTIFY() OWNER TO POSTGRES;

CREATE TRIGGER TBLEXAMPLE_AFTER_STATEMENT
    AFTER INSERT OR UPDATE OR DELETE
    ON TBLEXAMPLE
    FOR EACH ROW
EXECUTE PROCEDURE NOTIFY();

CREATE TRIGGER TBLEXAMPLE_BEFORE_TRUNCATE
    BEFORE TRUNCATE
    ON TBLEXAMPLE
    FOR EACH STATEMENT
EXECUTE PROCEDURE NOTIFY();



-- TRUNCATE PUBLIC.TBLEXAMPLE;