--建立 table (要記得改 XXX)
CREATE TABLE IF NOT EXISTS public.table_monitor
(
    table_name text COLLATE pg_catalog."default" NOT NULL,
    update_at timestamp without time zone NOT NULL DEFAULT now(),
    update_count bigint NOT NULL DEFAULT 0,
    CONSTRAINT table_monitor_pkey PRIMARY KEY (table_name)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.table_monitor
    OWNER to XXX;

-- 這一段只要執行一次
CREATE OR REPLACE FUNCTION public.table_monitor_trigger_fnc()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
     Update public.table_monitor Set update_count = update_count + 1, update_at = now()  Where table_name = TG_TABLE_NAME; 
RETURN NEW;
END;
$BODY$;

ALTER FUNCTION public.table_monitor_trigger_fnc()
    OWNER TO XXX;

-- 對於被監控的 table, 要執行以下的指令，記得 xxxx 要改為 table 的名字(要符合大小寫)
INSERT INTO public.table_monitor (table_name, update_at, update_count) VALUES ('xxxx', '2000-01-01', 0) ON CONFLICT DO NOTHING;

CREATE OR REPLACE TRIGGER table_monitor_trigger
    AFTER INSERT OR DELETE OR UPDATE 
    ON xxxx
    FOR EACH STATEMENT
    EXECUTE FUNCTION public.table_monitor_trigger_fnc();