-- 創(chuàng)建審計(jì)schema（如果已存在可以跳過(guò)）
CREATE SCHEMA IF NOT EXISTS audit;

-- 創(chuàng)建通用審計(jì)觸發(fā)器函數(shù)
CREATE OR REPLACE FUNCTION audit.create_audit_trigger(
    origin_schema TEXT,     -- 原始表所在schema
    origin_table TEXT,      -- 原始表名
    audit_schema TEXT DEFAULT 'audit'  -- 審計(jì)表所在schema，默認(rèn)為audit
)
RETURNS void AS $$
DECLARE
    backup_table TEXT;
    trigger_name TEXT;
    trigger_func_name TEXT;
BEGIN
    -- 構(gòu)造備份表名
    backup_table := origin_table || '_bak';
    -- 構(gòu)造觸發(fā)器名
    trigger_name := origin_table || '_audit';
    -- 構(gòu)造觸發(fā)器函數(shù)名
    trigger_func_name := origin_table || '_audit_func';
    
    -- 創(chuàng)建備份表
    EXECUTE format('
        DROP TABLE IF EXISTS %I.%I;
        CREATE TABLE %I.%I AS 
        SELECT 
            ''''::text as operation,
            now()::timestamp operation_time,
            ''''::text user_name,
            ''''::text old_content,
            * 
        FROM %I.%I
        WHERE 1=0;
    ', audit_schema, backup_table, audit_schema, backup_table, origin_schema, origin_table);

    -- 創(chuàng)建觸發(fā)器函數(shù)
    EXECUTE format('
        CREATE OR REPLACE FUNCTION %I.%I() RETURNS TRIGGER AS $func$
        BEGIN
            IF (TG_OP = ''DELETE'') THEN
                INSERT INTO %I.%I 
                SELECT TG_OP, now(), current_user, '''', OLD.*;
                RETURN OLD;
            ELSIF (TG_OP = ''UPDATE'') THEN
                INSERT INTO %I.%I 
                SELECT TG_OP, now(), current_user, row_to_json(OLD.*), NEW.*;
                RETURN NEW;
            ELSIF (TG_OP = ''INSERT'') THEN
                INSERT INTO %I.%I 
                SELECT TG_OP, now(), current_user, '''', NEW.*;
                RETURN NEW;
            END IF;
            RETURN NULL;
        END;
        $func$ LANGUAGE plpgsql;
    ', audit_schema, trigger_func_name, audit_schema, backup_table, audit_schema, backup_table, audit_schema, backup_table);

    -- 創(chuàng)建觸發(fā)器
    EXECUTE format('
        DROP TRIGGER IF EXISTS %I ON %I.%I;
        CREATE TRIGGER %I
            AFTER INSERT OR UPDATE OR DELETE ON %I.%I
            FOR EACH ROW EXECUTE PROCEDURE %I.%I();
    ', trigger_name, origin_schema, origin_table, trigger_name, origin_schema, origin_table, audit_schema, trigger_func_name);
    
END;
$$ LANGUAGE plpgsql;

-- 為 example.test_info 表創(chuàng)建審計(jì)
SELECT audit.create_audit_trigger('example', 'test_info');

-- 為 other_schema.employee 表創(chuàng)建審計(jì)，并指定審計(jì)表存放在 custom_audit schema中
SELECT audit.create_audit_trigger('other_schema', 'employee', 'custom_audit');

-- 查詢(xún)最近的操作記錄
SELECT operation, operation_time, user_name, id, name 
FROM audit.test_info_bak 
ORDERBY operation_time DESC
LIMIT 100;

-- 查詢(xún)特定用戶(hù)的操作
SELECT*FROM audit.test_info_bak 
WHERE user_name ='postgres'
ORDERBY operation_time DESC;

-- 查詢(xún)特定記錄的變更歷史
SELECT operation, operation_time, user_name, old_content, name, status
FROM audit.test_info_bak 
WHERE id =123
ORDERBY operation_time;