先创建一个表格用来记录跟踪信息:
create table "temp".create_drop_alter_ddl_audit
(
c_key bigserial primary key,--行号id
c_time timestamp, -- 触发时间
c_user varchar(64), -- 触发用户
c_txn varchar(16), -- current transaction
c_tag varchar(24), -- 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'
c_oid integer, -- For future use - TG_OBJECTID
c_name varchar(64), -- For future use - TG_OBJECTNAME
c_schema varchar(64), -- 所属模式名称
c_ddlqry text -- 触发语句
)
触发器函数编写如下:
CREATE OR REPLACE FUNCTION create_drop_alter_ddl()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
declare _qry text;
BEGIN
if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE')