BEGIN;

CREATE SCHEMA audit_ddl;
SET LOCAL search_path TO audit_ddl;

CREATE FUNCTION audit_ddl_on_start()
  RETURNS event_trigger
  LANGUAGE plpgsql
AS $on_start$
DECLARE
    lockid INTEGER := 31337;
BEGIN
    -- make sure we only create the temp table (and take a snapshot)
    -- once per transaction.
    PERFORM 1 FROM pg_locks
        WHERE locktype='advisory'
        AND objid = lockid AND objsubid = 1
        AND pid=pg_backend_pid();
    IF NOT FOUND THEN
        -- set the flag to disable future audit_ddl_on_start() calls.
        PERFORM pg_advisory_xact_lock_shared(lockid);
        RAISE NOTICE 'on_start: taking catalog snapshot...';
        -- take snapshots of the catalog.
        -- NOTE:  the view's key is (reloid, attnum):  reloid never changes,
        --        and attnum is never reused.
        CREATE OR REPLACE TEMPORARY VIEW relation_columns AS
            SELECT pg_class.oid AS reloid, nspname, relname, attnum, attname
            FROM pg_class 
                JOIN pg_namespace ON (relnamespace = pg_namespace.oid) 
                JOIN pg_attribute ON (attrelid = pg_class.oid)
            WHERE relkind IN ('r', 'v') AND attnum > 0 AND NOT attisdropped
            AND nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema';
        CREATE TEMPORARY TABLE relation_columns_snapshot 
            ON COMMIT DROP
            AS SELECT * FROM relation_columns;
        -- queue a call to audit_ddl_on_commit() for transaction commit.
        CREATE TEMPORARY TABLE on_commit_table (foo TEXT) ON COMMIT DROP;
        CREATE CONSTRAINT TRIGGER on_commit_trigger
            AFTER INSERT ON on_commit_table
            DEFERRABLE INITIALLY DEFERRED
            FOR EACH ROW EXECUTE PROCEDURE audit_ddl.audit_ddl_on_commit();
        INSERT INTO on_commit_table (foo) VALUES ('');
    END IF;
END;
$on_start$;

CREATE FUNCTION audit_ddl_on_commit()
  RETURNS trigger
  LANGUAGE plpgsql
AS $on_commit$
DECLARE
    result RECORD;
    message TEXT;
BEGIN
    RAISE NOTICE 'on_commit: checking for DDL actions.';
    FOR result IN
        SELECT DISTINCT
               curr.reloid::regclass AS rel,
               prev.nspname AS prevnsp,
               prev.relname AS prevname,
               curr.relname AS currname
        FROM relation_columns AS curr
            FULL OUTER JOIN relation_columns_snapshot AS prev
            ON (prev.reloid = curr.reloid)
        WHERE prev.relname <> curr.relname
        AND prev IS NOT NULL AND curr IS NOT NULL
    LOOP
        message := format('table %I.%I renamed to %s.',
                          result.prevnsp, result.prevname, result.rel);
        RAISE NOTICE 'on_commit: %', message;
    END LOOP;
    FOR result IN
        SELECT curr.reloid::regclass AS rel,
               prev.attname AS prevname,
               curr.attname AS currname
        FROM relation_columns AS curr
            FULL OUTER JOIN relation_columns_snapshot AS prev
            ON (prev.reloid = curr.reloid AND prev.attnum = curr.attnum)
        WHERE prev.attname <> curr.attname
        AND prev IS NOT NULL AND curr IS NOT NULL
    LOOP
        message := format('table %s column %I renamed to %I.',
                          result.rel, result.prevname, result.currname);
        RAISE NOTICE 'on_commit: %', message;
    END LOOP;
    RETURN NULL;
END;
$on_commit$;

CREATE EVENT TRIGGER audit_ddl_event_trigger
    ON ddl_command_start EXECUTE PROCEDURE audit_ddl_on_start();

COMMIT;
