PostgreSQL如何动态执行表中存储的SQL语句并校验查询结果

基础实现步骤 #

你可以通过PL/pgSQL编写存储过程实现需求，支持遍历启用状态的校验规则、动态执行SQL、留存执行结果、失败抛出异常，完整代码如下：

1. 首先创建校验结果存储表，用于留存所有校验的执行记录

CREATE TABLE data_check_results (
    check_run_id bigserial PRIMARY KEY,
    check_name varchar NOT NULL,
    execute_sql text NOT NULL,
    actual_result text,
    is_check_passed boolean,
    error_msg text,
    execute_time timestamptz DEFAULT now()
);

2. 创建执行校验的存储过程

CREATE OR REPLACE PROCEDURE run_all_active_data_checks(throw_error_on_failure boolean DEFAULT false)
LANGUAGE plpgsql
AS $$
DECLARE
    current_check record;
    returned_count bigint;
    exec_error text;
    failed_count int := 0;
BEGIN
    -- 遍历所有isactive=true的校验规则
    FOR current_check IN 
        SELECT checkname, sqlstring 
        FROM TheseAreDataChecks 
        WHERE isactive = true
    LOOP
        exec_error := NULL;
        returned_count := NULL;
        
        BEGIN
            -- 动态执行校验SQL，当前适配返回单统计值的校验语句（比如count查询）
            EXECUTE current_check.sqlstring INTO returned_count;
        EXCEPTION WHEN OTHERS THEN
            -- 捕获SQL执行阶段的异常
            GET STACKED DIAGNOSTICS exec_error = MESSAGE_TEXT;
        END;

        -- 写入执行结果
        INSERT INTO data_check_results(check_name, execute_sql, actual_result, is_check_passed, error_msg)
        VALUES (
            current_check.checkname,
            current_check.sqlstring,
            returned_count::text,
            CASE WHEN exec_error IS NULL THEN true ELSE false END,
            exec_error
        );

        IF exec_error IS NOT NULL THEN
            failed_count := failed_count + 1;
        END IF;
    END LOOP;

    -- 根据入参决定是否在存在失败项时抛出错误
    IF throw_error_on_failure = true AND failed_count > 0 THEN
        RAISE EXCEPTION '共 % 个数据校验执行失败，详情可查询data_check_results表', failed_count;
    END IF;
END;
$$;

3. 调用方式

-- 入参传true表示存在执行失败时直接抛出错误，传false则仅记录结果不中断流程
CALL run_all_active_data_checks(true);

-- 查询校验结果
SELECT * FROM data_check_results;

如果需要实现自动比对预期结果的能力，只需要给TheseAreDataChecks表新增expected_value（预期返回值）、compare_operator（比较符，如=、>、<）字段，在存储过程中增加实际值和预期值的比对逻辑即可，不需要调整整体执行框架。如果你的校验语句需要返回异常明细而非单统计值，可以将actual_result字段类型改为jsonb，通过动态SQL将返回的结果集转为jsonb存储。

校验逻辑设计最佳实践 #

优先选择先全量落盘所有校验执行结果，再基于结果做预期比对的方案，不推荐把校验逻辑直接内置在查询语句中，原因如下：

• 可追溯性强：所有校验的实际返回值、执行时间、报错信息全量留存，排查问题时不需要重跑任务，直接查询历史记录即可；内置校验逻辑通常只返回是否通过，丢失实际值，排查时需要手动复现
• 维护成本低：校验规则、预期阈值的调整只需要修改配置表数据，不需要改动执行框架的代码，规则和执行逻辑完全解耦
• 容错性更好：单条校验SQL执行报错不会中断整个校验流程，所有规则的执行状态都能完整记录；内置逻辑的模式下单条语句报错很容易终止整个任务，无法拿到其他规则的校验结果
  只有当校验场景极简单、不需要留存历史记录、且校验SQL本身直接返回布尔值结果时，才考虑把校验逻辑内置在SQL语句中，生产环境的数据校验场景不推荐该方式。

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