如何在PostgreSQL中创建存储过程循环调用DailyCalculation处理日期范围？

问题 #

我有一个PostgreSQL存储过程schema.DailyCalculation，用于从某张表聚合数据并将结果存入另一张表供PowerBI报表展示。该过程按日聚合数据，接受startDate和endDate参数（endDate始终为startDate + 1），定义如下：

CREATE OR REPLACE PROCEDURE schema.DailyCalculation(
    "startDate" timestamp without time zone,
    "endDate" timestamp without time zone)
AS $$
DECLARE
   -- 变量声明
BEGIN
   -- 数据聚合逻辑
END;
$$ LANGUAGE plpgsql;

现在发现历史数据计算方式有误，需要针对一个日期范围批量调用该存储过程。为了拆分任务，希望仍以单日区间调用DailyCalculation，因此需要创建新存储过程schema.RecalculateDailyData，传入长日期范围后，自动在范围内以单日区间反复调用DailyCalculation。

例如调用：

CALL schema.RecalculateDailyData('2023-01-01', '2023-01-31');

需等效于执行：

CALL schema.DailyCalculation('2023-01-01', '2023-01-02');
CALL schema.DailyCalculation('2023-01-02', '2023-01-03');
...
CALL schema.DailyCalculation('2023-01-30', '2023-01-31');

我已经能生成对应动态SQL的查询语句：

SELECT CONCAT('CALL schema.DailyCalculation(''', date_trunc('day', dd)::date, ''', ''', date_trunc('day', dd)::date + 1, ''');') 
FROM generate_series( '2023-01-01'::timestamp, '2023-01-31'::timestamp, '1 day'::interval) dd;

请问如何遍历该结果集并执行这些动态SQL语句？

解决方案 #

优先推荐直接循环调用存储过程的方式，无需拼接动态SQL，更安全且可读性更强：

创建批量重算存储过程 #

CREATE OR REPLACE PROCEDURE schema.RecalculateDailyData(
    p_start_date date,
    p_end_date date)
AS $$
DECLARE
    current_day date;
BEGIN
    -- 遍历日期范围中的每一天（结束日期减1天，避免生成超出需求的调用）
    FOR current_day IN
        SELECT generate_series(p_start_date, p_end_date - INTERVAL '1 day', '1 day')::date
    LOOP
        -- 调用单日计算存储过程，传入当日和次日作为区间
        CALL schema.DailyCalculation(current_day::timestamp, (current_day + INTERVAL '1 day')::timestamp);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

调用方式 #

直接执行你预期的语句即可：

CALL schema.RecalculateDailyData('2023-01-01', '2023-01-31');

若坚持使用动态SQL执行（不推荐，存在SQL注入风险） #

如果一定要基于你生成的动态SQL来执行，可以用FOR...IN遍历查询结果，再通过EXECUTE执行每条语句：

CREATE OR REPLACE PROCEDURE schema.RecalculateDailyData(
    p_start_date date,
    p_end_date date)
AS $$
DECLARE
    sql_stmt text;
BEGIN
    FOR sql_stmt IN
        SELECT CONCAT('CALL schema.DailyCalculation(''', date_trunc('day', dd)::date, ''', ''', date_trunc('day', dd)::date + 1, ''');') 
        FROM generate_series(p_start_date::timestamp, p_end_date::timestamp, '1 day'::interval) dd
        WHERE date_trunc('day', dd)::date < p_end_date -- 过滤最后一天，避免无效调用
    LOOP
        EXECUTE sql_stmt;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

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