PostgreSQL存储过程：如何将时间段对比分析结果写入并追加到表

核心问题修正与实现步骤 #

首先需要创建用于存储对比结果的目标表（如果尚未存在）：

CREATE TABLE IF NOT EXISTS schema.tbl_bv_comparison (
    client VARCHAR,
    client_sector VARCHAR,
    segmentation VARCHAR,
    rank_source VARCHAR,
    region VARCHAR,
    asset_class VARCHAR,
    products VARCHAR,
    currency_pair VARCHAR,
    product_type VARCHAR,
    period VARCHAR,
    bv_rank VARCHAR
);

修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE schema.sp_test_bv()
LANGUAGE plpgsql
AS $$
DECLARE
    -- 直接遍历时间段对表的每条记录，避免数组下标错误
    rec RECORD;
BEGIN
    -- 清空目标表（可选，若需要每次运行全量覆盖则保留，否则删除此句实现追加）
    -- TRUNCATE TABLE schema.tbl_bv_comparison;

    -- 遍历所有需要对比的时间段对
    FOR rec IN SELECT p1, p2 FROM schema.tbl_bv_temp_timeperiod LOOP
        -- 将对比结果插入目标表，无需临时表，直接用子查询关联
        INSERT INTO schema.tbl_bv_comparison (
            client, client_sector, segmentation, rank_source, region, asset_class,
            products, currency_pair, product_type, period, bv_rank
        )
        SELECT 
            x.client, x.client_sector, x.segmentation, x.rank_source, x.region, x.asset_class,
            x.products, x.currency_pair, x.product_type,
            CONCAT(SPLIT_PART(rec.p1, ' ', 1), ' ', RIGHT(SPLIT_PART(rec.p1, ' ', 2), 2), ' vs. ', 
                   SPLIT_PART(rec.p2, ' ', 1), ' ', RIGHT(SPLIT_PART(rec.p2, ' ', 2), 2)) AS period,
            CASE
                WHEN (x.bv_rank::INT - y.bv_rank::INT) > 0 THEN CONCAT((x.bv_rank::INT - y.bv_rank::INT)::VARCHAR, '  🢁')
                WHEN (x.bv_rank::INT - y.bv_rank::INT) < 0 THEN CONCAT((x.bv_rank::INT - y.bv_rank::INT)::VARCHAR, '  🢃')
                ELSE CONCAT((x.bv_rank::INT - y.bv_rank::INT)::VARCHAR, '  🢂')
            END AS bv_rank
        FROM (
            -- 子查询获取第一个时间段的数据
            SELECT 
                client, client_sector, segmentation, rank_source, region, asset_class,
                products, currency_pair, product_type, bv_rank
            FROM schema.tbl_bv_temp
            WHERE 
                period_new = SPLIT_PART(rec.p1, ' ', 1) 
                AND year = SPLIT_PART(rec.p1, ' ', 2)::INT
                AND bv_rank NOT LIKE ALL(ARRAY['Top%', '%-%', 'Tier%'])
        ) x
        LEFT JOIN (
            -- 子查询获取第二个时间段的数据
            SELECT 
                client, client_sector, segmentation, rank_source, region, asset_class,
                products, currency_pair, product_type, bv_rank
            FROM schema.tbl_bv_temp
            WHERE 
                period_new = SPLIT_PART(rec.p2, ' ', 1) 
                AND year = SPLIT_PART(rec.p2, ' ', 2)::INT
                AND bv_rank NOT LIKE ALL(ARRAY['Top%', '%-%', 'Tier%'])
        ) y ON 
            x.client = y.client 
            AND x.client_sector = y.client_sector 
            AND x.segmentation = y.segmentation 
            AND x.rank_source = y.rank_source 
            AND x.region = y.region 
            AND x.asset_class = y.asset_class 
            AND x.products = y.products 
            AND x.currency_pair = y.currency_pair 
            AND x.product_type = y.product_type;

        RAISE INFO '已处理时间段对: % vs %', rec.p1, rec.p2;
    END LOOP;
END;
$$;

关键修改说明 #

1. 变量遍历优化：放弃原数组下标循环，改用FOR rec IN SELECT ...直接遍历tbl_bv_temp_timeperiod的每条记录，避免数组越界或遍历次数错误的问题。
2. 结果存储实现：将原SELECT语句改为INSERT INTO ... SELECT ...，直接把对比结果插入目标表，实现数据追加。如果需要每次运行清空旧数据，可以保留TRUNCATE TABLE语句。
3. 临时表移除：用子查询替代临时表t1、t2，简化逻辑并提升执行效率，避免每次循环创建/删除临时表的开销。
4. 语法错误修正：删除原错误的变量声明i INT:=1 record;，改为正确的记录类型变量rec RECORD。
5. DATESTYLE设置移除：原设置对字符串拆分逻辑无影响，因此删除以简化代码。

使用方法 #

1. 先创建目标表（若未存在）。
2. 执行存储过程：

CALL schema.sp_test_bv();

3. 查询对比结果：

SELECT * FROM schema.tbl_bv_comparison;

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