PostgreSQL高效按索引截取字符串：审计触发器提取用户信息

1. 给审计表新增存储字段 #

先执行SQL给你的审计表添加对应字段：

ALTER TABLE your_audit_table 
ADD COLUMN created_by TEXT,
ADD COLUMN updated_by TEXT;

替换your_audit_table为实际审计表名。

2. 在触发器函数中实现字符串提取逻辑 #

下面提供两种实现方式，分别对应你熟悉的indexOf+substring手动截取，以及更简洁的正则表达式提取：

方式一：模拟indexOf+substring手动截取

完全对应Java字符串操作逻辑，适合格式固定的查询文本：

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
    query_text TEXT := current_query();
    created_by_val TEXT;
    updated_by_val TEXT;
    start_idx INT;
    end_idx INT;
BEGIN
    -- 提取created_by的值
    start_idx := position('created_by => ' IN query_text);
    IF start_idx > 0 THEN
        start_idx := start_idx + length('created_by => ');
        end_idx := position(', ' IN query_text FROM start_idx);
        IF end_idx = 0 THEN
            end_idx := length(query_text) + 1;
        END IF;
        created_by_val := trim(substring(query_text FROM start_idx FOR end_idx - start_idx));
    END IF;

    -- 提取updated_by的值，逻辑一致
    start_idx := position('updated_by => ' IN query_text);
    IF start_idx > 0 THEN
        start_idx := start_idx + length('updated_by => ');
        end_idx := position(', ' IN query_text FROM start_idx);
        IF end_idx = 0 THEN
            end_idx := length(query_text) + 1;
        END IF;
        updated_by_val := trim(substring(query_text FROM start_idx FOR end_idx - start_idx));
    END IF;

    -- 替换为实际审计表插入逻辑，存入提取到的用户值
    INSERT INTO your_audit_table (operation_type, table_name, created_by, updated_by)
    VALUES (TG_OP, TG_TABLE_NAME, created_by_val, updated_by_val);

    RETURN NULL; -- 行级触发器可根据需求返回NEW/OLD
END;
$$ LANGUAGE plpgsql;

方式二：正则表达式简化提取

格式固定时用正则更简洁，处理边界情况更灵活：

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
    query_text TEXT := current_query();
    created_by_val TEXT;
    updated_by_val TEXT;
BEGIN
    -- 提取created_by，匹配"created_by => "后到下一个逗号的内容
    created_by_val := trim(substring(query_text FROM 'created_by => ([^,]+)'));
    -- 提取updated_by，逻辑同理
    updated_by_val := trim(substring(query_text FROM 'updated_by => ([^,]+)'));

    -- 审计表插入逻辑，同上
    INSERT INTO your_audit_table (operation_type, table_name, created_by, updated_by)
    VALUES (TG_OP, TG_TABLE_NAME, created_by_val, updated_by_val);

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

注意事项 #

• 如果用户值带引号（比如created_by => 'Jason'），可调整正则去掉引号，例如改成created_by => '([^']+)'或created_by => "([^"]+)"。
• 若用户值包含逗号，手动截取会出错，需调整正则匹配逻辑（比如匹配到下一个字段名前）。

内容的提问来源于stack exchange，提问作者Jimmy_R