CREATE OR REPLACE FUNCTION information_schema.notice_ddl_event() RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
log text;
activity RECORD;
BEGIN

    select CASE WHEN COALESCE(current_setting('notice_ddl_event.log',true),'off') = '' THEN 'off' WHEN COALESCE(current_setting('notice_ddl_event.log',true),'off') = 'off' THEn 'off' ELSE 'on' END INTO log;
    IF log = 'on' THEN
      RAISE INFO 'TAG: %   EVENT: %', tg_tag, tg_event;
    END IF;
    IF tg_tag != 'DROP TABLE' AND tg_event != 'sql_drop' THEN
      FOR r IN SELECT *,clock_timestamp() as "time",current_query() as current_query FROM pg_event_trigger_ddl_commands() LOOP
        select usename,application_name,client_addr,client_port,pid ,query,backend_xid from pg_stat_activity where pid=pg_backend_pid() INTO activity;
        EXECUTE 'insert into information_schema.ddl_events (timest,classid,objid,objsubid,tag,object_type,object_identity,pid,query,usename,application_name,client_addr,client_port,backend_xid,current_query) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15);'
          USING r.time, r.classid, r.objid, r.objsubid, r.command_tag, r.object_type, r.object_identity,activity.pid,activity.query,activity.usename,activity.application_name,activity.client_addr,activity.client_port,activity.backend_xid,r.current_query;
        IF log = 'on' THEN
          RAISE INFO '% :  % : % : % : % : % : % '
                      ,r.time
                      ,r.classid
                      ,r.objid
                      ,r.objsubid
                      ,r.command_tag
                      ,r.object_type
                      ,r.object_identity
                      ;

        END IF;  --- log
      END LOOP;
    END IF;

    IF tg_event = 'sql_drop' THEN
      FOR r IN SELECT *, clock_timestamp() as "time",current_query() as current_query FROM pg_event_trigger_dropped_objects()
        LOOP
          IF r.is_temporary != true  THEN
            select usename,application_name,client_addr,client_port,pid,query,backend_xid from pg_stat_activity where pid=pg_backend_pid() INTO activity;
            EXECUTE 'insert into information_schema.ddl_events (timest,classid,objid,objsubid,tag,object_type,object_identity,event_tag,pid,query,usename,application_name,client_addr,client_port,backend_xid,current_query) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16);'
              USING r.time, r.classid, r.objid, r.objsubid, tg_tag, r.object_type, r.object_identity,'DROP',activity.pid,activity.query,activity.usename,activity.application_name,activity.client_addr,activity.client_port,activity.backend_xid,r.current_query;
            IF log = 'on' THEN
              RAISE INFO '% :  % : % : % : % : % : %'
                        ,r.time
                        ,r.classid
                        ,r.objid
                        ,r.objsubid
                        ,tg_tag
                        ,r.object_type
                        ,r.object_identity
                        ;
            END IF;  --- log
          END IF;    --- drop
      END LOOP;
    END IF;
END;
$$;


------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS information_schema.ddl_events (
    id serial,                           ---
    timest timestamp without time zone,  --- clock_timestamp()
    classid oid,                         --- pg_event_trigger_ddl_commands().classid/pg_event_trigger_dropped_objects().classid
    objid oid,                           --- pg_event_trigger_ddl_commands().objid/pg_event_trigger_dropped_objects().objid
    objsubid integer,                    --- pg_event_trigger_ddl_commands().objsubid/pg_event_trigger_dropped_objects().objsubid
    tag text,                            --- pg_event_trigger_ddl_commands().command_tag/TG_TAG
    object_type text,                    --- pg_event_trigger_ddl_commands().object_type/pg_event_trigger_dropped_objects().object_type
    object_identity text,                --- pg_event_trigger_ddl_commands().object_identity/pg_event_trigger_dropped_objects().object_identity
    event_tag text,                      --- /"DROP"
    query text,                          --- pg_stat_activity.query
    current_query text,                  --- current_query()
    pid integer,                         --- pg_stat_activity.pid
    usename text,                        --- pg_stat_activity.usename
    application_name text,               --- pg_stat_activity.application_name
    client_addr text,                    --- pg_stat_activity.client_addr
    client_port text,                    --- pg_stat_activity.client_port
    backend_xid xid,                      --- pg_stat_activity.backend_xid
    export_time timestamp without time zone --- проставляется программой централизованного сбора статистики
);


------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
CREATE EVENT TRIGGER tr_event_drops
   ON sql_drop
   EXECUTE FUNCTION information_schema.notice_ddl_event();

CREATE EVENT TRIGGER tr_event_no_drops
  ON ddl_command_end
  EXECUTE FUNCTION information_schema.notice_ddl_event();