如何利用Postgres自动计算Supabase饮食表的营养统计值？

方案1：用视图实时计算（推荐） #

直接创建视图替代days_of_diet表中的统计字段，完全不需要手动维护，数据永远和meals表同步：

CREATE VIEW days_of_diet_stats AS
SELECT
    d.id, -- 假设days_of_diet有主键id
    d.meal_ids,
    COALESCE(SUM(m.calories), 0) AS total_calories,
    COALESCE(SUM(m.protein), 0) AS total_protein,
    COALESCE(SUM(m.carbs), 0) AS total_carbs,
    -- 保留days_of_diet的其他字段
    d.other_fields
FROM days_of_diet d
LEFT JOIN meals m ON m.id = ANY(d.meal_ids)
GROUP BY d.id, d.meal_ids, d.other_fields;

优势 #

• 无冗余数据，彻底避免数据不一致问题
• 无需编写触发器或后端逻辑，Postgres自动实时计算
• Supabase中可直接查询该视图，使用方式与普通表完全一致

方案2：用触发器维护表中存储的统计值 #

如果必须将统计值存在days_of_diet表中（比如需要为统计字段创建索引优化查询性能），可以通过触发器自动更新：

步骤1：创建更新统计值的函数 #

CREATE OR REPLACE FUNCTION update_diet_day_totals()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新当前饮食日的统计值
    UPDATE days_of_diet
    SET
        total_calories = (SELECT COALESCE(SUM(calories), 0) FROM meals WHERE id = ANY(meal_ids)),
        total_protein = (SELECT COALESCE(SUM(protein), 0) FROM meals WHERE id = ANY(meal_ids)),
        total_carbs = (SELECT COALESCE(SUM(carbs), 0) FROM meals WHERE id = ANY(meal_ids))
    WHERE id = NEW.id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：为days_of_diet表添加触发器（餐食ID数组变化时触发） #

CREATE TRIGGER trigger_diet_day_meal_ids_change
AFTER INSERT OR UPDATE OF meal_ids ON days_of_diet
FOR EACH ROW
EXECUTE FUNCTION update_diet_day_totals();

步骤3：为meals表添加触发器（营养数值变化时触发） #

CREATE OR REPLACE FUNCTION update_related_diet_days()
RETURNS TRIGGER AS $$
BEGIN
    -- 找到所有包含当前餐食ID的饮食日记录并更新
    UPDATE days_of_diet
    SET
        total_calories = (SELECT COALESCE(SUM(calories), 0) FROM meals WHERE id = ANY(meal_ids)),
        total_protein = (SELECT COALESCE(SUM(protein), 0) FROM meals WHERE id = ANY(meal_ids)),
        total_carbs = (SELECT COALESCE(SUM(carbs), 0) FROM meals WHERE id = ANY(meal_ids))
    WHERE OLD.id = ANY(meal_ids);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_meal_nutrition_change
AFTER UPDATE OF calories, protein, carbs ON meals
FOR EACH ROW
EXECUTE FUNCTION update_related_diet_days();

补充：处理餐食删除场景 #

若需要支持删除餐食后自动更新统计值，给meals表添加删除触发器：

CREATE TRIGGER trigger_meal_delete
AFTER DELETE ON meals
FOR EACH ROW
EXECUTE FUNCTION update_related_diet_days();

选择建议 #

• 若查询频率高、对实时性要求高，优先选视图方案，架构更简洁易维护
• 若需要对统计字段创建索引或有极高的查询性能要求，再用触发器方案，但要注意触发器会增加写操作的开销

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