One post tagged with "ddl"

CREATE TABLE ddl_logs (
    log_id SERIAL PRIMARY KEY,
    event_type TEXT,
    object_type TEXT,
    schema_name TEXT,
    object_name TEXT,
    ddl_command TEXT,
    executed_by TEXT,
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        INSERT INTO ddl_logs (event_type, object_type, schema_name, object_name, ddl_command, executed_by)
        VALUES (r.command_tag, r.object_type, r.schema_name, r.object_identity, r.command, current_user);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER track_ddl_changes ON ddl_command_end
EXECUTE FUNCTION log_ddl_event();