asd

建立ddl_log表

CREATE TABLE "public"."ddl_log" (
  "id" SERIAL PRIMARY KEY,
  "event_type" varchar(50),
  "classid" varchar(255),
  "objid" varchar(255),
  "objsubid" varchar(255),
  "command_tag" varchar(255),
  "object_type" varchar(255),
  "object_name" varchar(255),
  "schema_name" varchar(255),
  "object_identity" varchar(255),
  "state" varchar(20) --表示该记录的处理状态 初始值init
);

For CREATE/ALTER/DROP

CREATE OR REPLACE FUNCTION ddl_log()
        RETURNS event_trigger 
LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        INSERT INTO ddl_log("event_type","classid","objid","objsubid","command_tag","object_type","schema_name","object_identity","state") 
            VALUES( 'ddl_command_end',
                    obj.classid,
                    obj.objid,
                    obj.objsubid,
                    obj.command_tag,
                    obj.object_type,
                    obj.schema_name,
                    obj.object_identity,
                    'init');
    END LOOP;
END
$$;

CREATE EVENT TRIGGER ddl_log_event
   ON ddl_command_end 
   EXECUTE FUNCTION ddl_log();

For DROP

CREATE OR REPLACE FUNCTION ddl_drop_log()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        INSERT INTO ddl_log("event_type","command_tag","classid","objid","objsubid","object_type","schema_name","object_name","object_identity","state") 
            VALUES( 'sql_drop',
                                        '',
                                        obj.classid,
                    obj.objid,
                    obj.objsubid,
                    obj.object_type,
                    obj.schema_name,
                                        obj.object_name,
                    obj.object_identity,
                    'init');
    END LOOP;
END
$$;


CREATE EVENT TRIGGER ddl_drop
   ON sql_drop
   EXECUTE FUNCTION ddl_drop_log();

test sql

CREATE TABLE test1(
    name TEXT
);
ALTER TABLE test1 add column name2 TEXT;

DROP TABLE test1;

get ddl record

SELECT * FROM ddl_log;

Event Trigger Functions 参考文档
https://www.postgresql.org/docs/12/functions-event-triggers.html

Event Trigger 触发事件（command_tag）详细列表
https://www.postgresql.org/docs/11/event-trigger-matrix.html