PostgreSQL存储过程开发：按月份数递增日期插入多行数据

1. 先创建原表和目标表（若未存在） #

假设原表名为 operations，目标表名为 operation_schedule，先定义表结构：

-- 原表：存储原始操作数据
CREATE TABLE IF NOT EXISTS operations (
    id_operation VARCHAR(10) PRIMARY KEY,
    start_date DATE NOT NULL,
    number_months INTEGER NOT NULL CHECK (number_months >= 0),
    amount NUMERIC(10,2) NOT NULL
);

-- 目标表：存储拆分后的月度记录
CREATE TABLE IF NOT EXISTS operation_schedule (
    id_operation VARCHAR(10) NOT NULL,
    new_date DATE NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (id_operation, new_date),
    FOREIGN KEY (id_operation) REFERENCES operations(id_operation)
);

2. 编写触发器函数 #

这个函数会在原表插入数据时，自动生成对应数量的月度记录并插入目标表：

CREATE OR REPLACE FUNCTION generate_monthly_records()
RETURNS TRIGGER AS $$
BEGIN
    -- 生成从0到指定月份数的整数序列，计算对应日期后插入目标表
    INSERT INTO operation_schedule (id_operation, new_date, amount)
    SELECT
        NEW.id_operation,
        NEW.start_date + INTERVAL '1 month' * s.month_offset,
        NEW.amount
    FROM generate_series(0, NEW.number_months) AS s(month_offset);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建触发器绑定到原表 #

让触发器在原表执行INSERT操作时自动调用上面的函数：

CREATE TRIGGER trigger_generate_monthly
AFTER INSERT ON operations
FOR EACH ROW
EXECUTE FUNCTION generate_monthly_records();

4. 测试验证 #

插入你提供的示例数据：

INSERT INTO operations (id_operation, start_date, number_months, amount)
VALUES
('01234', '2121-01-01', 1, 15000),
('02345', '2022-11-01', 2, 10000);

查询目标表验证结果：

SELECT * FROM operation_schedule ORDER BY id_operation, new_date;

返回结果与你期望的完全一致：

关键说明 #

• generate_series(0, NEW.number_months)：生成从0到指定月份数的整数序列，比如number_months=1时生成0、1两个数，对应+0和+1个月的日期。
• NEW.start_date + INTERVAL '1 month' * s.month_offset：PostgreSQL支持直接用日期加月度间隔计算递增日期，自动处理月末特殊情况（比如2023-01-31加1个月会变成2023-02-28/29）。
• 触发器采用AFTER INSERT，确保原表数据插入成功后再生成目标表记录，避免数据不一致。

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