Monitor Alteração Estrutura

-- SCHEMA: dba
--DROP SCHEMA IF EXISTS dba CASCADE;
CREATE SCHEMA IF NOT EXISTS dba
    AUTHORIZATION postgres;

-- Table: audit.ddl_history
-- DROP TABLE IF EXISTS dba.ddl_history;
CREATE TABLE IF NOT EXISTS dba.ddl_history
(
   id bigserial,
   xid bigint DEFAULT txid_current(),
   ddl_date timestamp with time zone,
   ddl_tag varchar,
   tipo varchar,
   schema_table varchar,
   extensao BOOLEAN,
   ddl_command TEXT,
    CONSTRAINT ddl_history_pkey PRIMARY KEY (id)
);
ALTER TABLE IF EXISTS audit.ddl_history OWNER to postgres;


-- FUNCTION: dba.log_ddl()
-- DROP FUNCTION IF EXISTS dba.log_ddl();
CREATE OR REPLACE FUNCTION dba.log_ddl()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
    SECUTIY DEFINER
AS $BODY$
DECLARE
  audit_query TEXT;
  vr RECORD;
  vignora TEXT[] = ARRAY['DROP TABLE', 'REFRESH MATERIALIZED VIEW'];
BEGIN
   IF NOT (tg_tag = ANY(vignora)) THEN
      FOR vr IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP

         INSERT INTO dba.ddl_history (ddl_date, ddl_tag, tipo, schema_table, extensao, ddl_command) VALUES (statement_timestamp(), tg_tag, vr.object_type, vr.object_identity, vr.in_extension, current_query());
      END LOOP;
   END IF;
END;
$BODY$;
ALTER FUNCTION dba.log_ddl() OWNER TO postgres;


-- Event Trigger: log_ddl_info on database logs
-- DROP EVENT TRIGGER IF EXISTS log_ddl_info;
CREATE EVENT TRIGGER log_ddl_info ON DDL_COMMAND_END
    EXECUTE PROCEDURE dba.log_ddl();
ALTER EVENT TRIGGER log_ddl_info OWNER TO postgres;


SELECT * FROM dba.ddl_history