dtxUtUyb

1-- SCHEMA: dba
2--DROP SCHEMA IF EXISTS dba CASCADE;
3CREATE SCHEMA IF NOT EXISTS dba
4    AUTHORIZATION postgres;
5
6-- Table: audit.ddl_history
7-- DROP TABLE IF EXISTS dba.ddl_history;
8CREATE TABLE IF NOT EXISTS dba.ddl_history
9(
10   id bigserial,
11   xid bigint DEFAULT txid_current(),
12   ddl_date timestamp with time zone,
13   ddl_tag varchar,
14   tipo varchar,
15   schema_table varchar,
16   extensao BOOLEAN,
17   ddl_command TEXT,
18    CONSTRAINT ddl_history_pkey PRIMARY KEY (id)
19);
20ALTER TABLE IF EXISTS audit.ddl_history OWNER to postgres;
21
22
23-- FUNCTION: dba.log_ddl()
24-- DROP FUNCTION IF EXISTS dba.log_ddl();
25CREATE OR REPLACE FUNCTION dba.log_ddl()
26    RETURNS event_trigger
27    LANGUAGE 'plpgsql'
28    COST 100
29    VOLATILE NOT LEAKPROOF
30AS $BODY$
31DECLARE
32  audit_query TEXT;
33  vr RECORD;
34  vignora TEXT[] = ARRAY['DROP TABLE', 'REFRESH MATERIALIZED VIEW'];
35BEGIN
36   IF NOT (tg_tag = ANY(vignora)) THEN
37      FOR vr IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
38
39         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());
40      END LOOP;
41   END IF;
42END;
43$BODY$;
44ALTER FUNCTION dba.log_ddl() OWNER TO postgres;
45
46    
47-- Event Trigger: log_ddl_info on database logs
48-- DROP EVENT TRIGGER IF EXISTS log_ddl_info;
49CREATE EVENT TRIGGER log_ddl_info ON DDL_COMMAND_END
50    EXECUTE PROCEDURE dba.log_ddl();
51ALTER EVENT TRIGGER log_ddl_info OWNER TO postgres;
52
53
54SELECT * FROM dba.ddl_history
55
56