PostgreSQL 14触发器函数中如何将列总和赋值给变量？

核心问题1：触发器时机错误 #

你使用的是AFTER UPDATE触发器，这种触发器在行已经写入数据库后执行，此时修改NEW变量的值不会同步到数据库中，导致计算出的size无法保存。需要将触发器改为BEFORE UPDATE，这样在更新行之前修改NEW的值，才能生效。

核心问题2：SUM函数返回NULL #

当SELECT SUM(size)没有匹配到任何行时，会返回NULL而非0。直接将NULL赋值给变量后，相加结果也会是NULL。需要用COALESCE函数将SUM的结果转为0，避免空值问题。

修正后的完整代码 #

CREATE TABLE folder(
    Id uuid unique,
    Url varchar(255),
    Date timestamp,
    ParentId uuid,
    Size int
);

CREATE TABLE file(
    Id uuid unique,
    Url varchar(255),
    Date timestamp,
    ParentId uuid,
    Size int,
    FOREIGN KEY (ParentId) REFERENCES Folder (Id) ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION update_dates_and_price_update_folder() RETURNS trigger AS $update_dates_and_price_update_folder$
DECLARE
    folders_sum integer;
    files_sum integer;
BEGIN
    -- 使用COALESCE确保无匹配行时返回0
    SELECT COALESCE(sum(size), 0) INTO folders_sum FROM file WHERE parentId = NEW.id;
    SELECT COALESCE(sum(size), 0) INTO files_sum FROM folder WHERE parentId = NEW.id;
    
    NEW.size = folders_sum + files_sum;
    
    -- 更新父文件夹的日期
    UPDATE folder set date = NEW.date WHERE id = NEW.parentId OR id = OLD.parentId;
    
    RETURN NEW;
END;
$update_dates_and_price_update_folder$ LANGUAGE plpgsql;

-- 改为BEFORE UPDATE触发器
CREATE OR REPLACE TRIGGER update_data_folder BEFORE UPDATE
    ON folder
    FOR ROW
    EXECUTE FUNCTION update_dates_and_price_update_folder();

额外说明 #

• 若需要在文件夹插入时也自动计算size，建议新增一个BEFORE INSERT触发器，复用同一个函数即可。
• 当移动文件夹（修改ParentId）时，原父文件夹的size也需要重新计算，当前代码只更新了父文件夹的日期，未更新其size，可根据需求补充该逻辑。

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