Event triggers: reacting to DDL

BEGIN;
CREATE FUNCTION log_ddl() RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    RAISE NOTICE 'ddl: % % %', r.command_tag, r.object_type, r.object_identity;
  END LOOP;
END $$;
CREATE EVENT TRIGGER audit_ddl ON ddl_command_end EXECUTE FUNCTION log_ddl();
CREATE TABLE et_demo (id int PRIMARY KEY);
ALTER TABLE et_demo ADD COLUMN note text;
SELECT evtname, evtevent, evtenabled FROM pg_event_trigger;
ROLLBACK;

BEGIN;
CREATE TABLE ddl_audit (
  at          timestamptz NOT NULL DEFAULT now(),
  role_name   text NOT NULL DEFAULT current_user,
  app         text DEFAULT current_setting('application_name', true),
  command     text NOT NULL,
  object_type text,
  identity    text
);
CREATE FUNCTION audit_ddl() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO ddl_audit (command, object_type, identity)
  SELECT command_tag, object_type, object_identity
  FROM pg_event_trigger_ddl_commands()
  WHERE object_identity <> 'public.ddl_audit';
END $$;
CREATE EVENT TRIGGER ddl_audit_trg ON ddl_command_end EXECUTE FUNCTION audit_ddl();
CREATE INDEX orders_status_tmp ON orders (status);
COMMENT ON TABLE orders IS 'customer orders';
SELECT role_name, command, object_type, identity FROM ddl_audit ORDER BY command;
ROLLBACK;

BEGIN;
CREATE FUNCTION no_drop() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  IF coalesce(current_setting('app.allow_drop', true), 'off') <> 'on' THEN
    RAISE EXCEPTION '% is blocked here; SET app.allow_drop = on in a reviewed session', tg_tag;
  END IF;
END $$;
CREATE EVENT TRIGGER guard_drop ON ddl_command_start WHEN TAG IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop();
CREATE TABLE et_tmp (id int);
DROP TABLE et_tmp;
ROLLBACK;

BEGIN;
CREATE FUNCTION log_drops() RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    RAISE NOTICE 'dropped % % (original: %)', r.object_type, r.object_identity, r.original;
  END LOOP;
END $$;
CREATE EVENT TRIGGER track_drops ON sql_drop EXECUTE FUNCTION log_drops();
CREATE TABLE et_a (id int PRIMARY KEY);
DROP TABLE et_a;
ROLLBACK;

BEGIN;
CREATE FUNCTION warn_rewrite() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'rewriting % (%), reason %', pg_event_trigger_table_rewrite_oid()::regclass,
    pg_size_pretty(pg_total_relation_size(pg_event_trigger_table_rewrite_oid())),
    pg_event_trigger_table_rewrite_reason();
END $$;
CREATE EVENT TRIGGER rewrite_guard ON table_rewrite EXECUTE FUNCTION warn_rewrite();
CREATE TABLE et_r (id int, note text);
INSERT INTO et_r SELECT g, 'x' FROM generate_series(1, 1000) g;
ALTER TABLE et_r ADD COLUMN flag boolean DEFAULT true;
ALTER TABLE et_r ALTER COLUMN id TYPE bigint;
ROLLBACK;

-- PostgreSQL 17+
CREATE FUNCTION check_login() RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  IF current_user = 'reporting' AND extract(hour FROM now()) BETWEEN 9 AND 17 THEN
    RAISE EXCEPTION 'reporting logins are not allowed during business hours';
  END IF;
END $$;
CREATE EVENT TRIGGER business_hours ON login EXECUTE FUNCTION check_login();

-- if a bug locks everyone out, a superuser connects with:
--   PGOPTIONS='-c event_triggers=off' psql -U postgres
ALTER EVENT TRIGGER business_hours DISABLE;