需使用Trigger、Function还是Procedure？自动更新汇总表代码咨询

现有代码的核心问题 #

1. 触发器触发时机错误：当前触发器绑定AFTER UPDATE事件，但需求是监听detailed_table的新增数据（INSERT），完全不匹配。
2. 触发器逻辑错误：summary_table是聚合统计表（按菜谱名和餐厅位置求和），现有触发器直接插入新行，会导致统计数据重复叠加，而非正确更新聚合值。
3. refresh_data函数存在语法/逻辑错误：
   • 字段名不一致：比如用了total_of_recipes但detailed_table定义的是total_recipes；
   • 表关联缺失：location_restaurant(restaurant.restaurant_id)中restaurant表未在JOIN中引入，导致语法错误；
   • 表名错误：film.title应为recipe.title（从上下文推断）。
4. 全量刷新与增量触发冲突：refresh_data是全量清空重插，和触发器的增量更新逻辑并存会导致数据混乱，需明确使用场景。

关于Function vs Procedure的疑问 #

PostgreSQL的触发器只能调用返回TRIGGER类型的函数，无法直接调用存储过程（Procedure），所以不需要改成Procedure，只需修正触发器函数的逻辑和触发时机即可。

修正后的代码示例 #

1. 修正触发器（实现新增数据时自动更新summary_table） #

-- 创建触发器函数：处理新增数据时更新聚合统计
CREATE OR REPLACE FUNCTION update_summary_on_detailed_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- 先尝试更新现有聚合行，不存在则插入（需先给summary_table加唯一约束）
    INSERT INTO summary_table (titles_of_recipes, total_recipes, location_restaurant)
    VALUES (NEW.titles_of_recipes, NEW.total_recipes, NEW.location_restaurant)
    ON CONFLICT (titles_of_recipes, location_restaurant)
    DO UPDATE SET total_recipes = summary_table.total_recipes + EXCLUDED.total_recipes;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器：监听detailed_table的INSERT事件
CREATE TRIGGER summary_after_detailed_insert
AFTER INSERT ON detailed_table
FOR EACH ROW
EXECUTE FUNCTION update_summary_on_detailed_insert();

2. 给summary_table添加唯一约束（保证ON CONFLICT生效） #

ALTER TABLE summary_table ADD CONSTRAINT unique_recipe_location UNIQUE (titles_of_recipes, location_restaurant);

3. 修正refresh_data全量刷新函数 #

CREATE OR REPLACE FUNCTION refresh_data()
RETURNS VOID AS $$
BEGIN
    DELETE FROM detailed_table;
    DELETE FROM summary_table;

    -- 修正表关联和字段名错误
    INSERT INTO detailed_table (total_recipes, inventory_id, location_restaurant, titles_of_recipes)
    SELECT
        COUNT(r.recipe_id) AS total_recipes,
        i.inventory_id AS inventory_id,
        res.location_restaurant AS location_restaurant,
        r.title AS titles_of_recipes
    FROM 
       recipes r
    JOIN inventory i ON r.recipe_id = i.recipe_id
    JOIN store s ON i.store_id = s.store_id
    JOIN restaurant res ON s.restaurant_id = res.restaurant_id -- 补充restaurant表关联
    GROUP BY
        i.inventory_id, res.location_restaurant, r.title
    ORDER BY
        total_recipes DESC;

    -- 修正summary_table的聚合插入逻辑
    INSERT INTO summary_table (titles_of_recipes, total_recipes, location_restaurant)
    SELECT
        titles_of_recipes,
        SUM(total_recipes) AS total_recipes,
        location_restaurant
    FROM detailed_table
    GROUP BY
        titles_of_recipes, location_restaurant;

    RETURN;
END;
$$ LANGUAGE plpgsql;

-- 初始化全量数据
SELECT refresh_data();

额外说明 #

• 如果需要支持detailed_table的更新/删除操作同步summary_table，可以创建对应触发器：
  • UPDATE时：先减去旧值，再加上新值
  • DELETE时：减去对应数值
• 全量刷新函数refresh_data和增量触发器可以共存，但调用全量刷新前会清空所有数据，需注意业务场景。

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