PostgreSQL通过触发器从出生日期计算年龄返回空值问题求解

问题排查 #

你的触发器代码存在以下3个核心错误：

• 触发器触发时机和触发级别错误：要修改待写入的行数据（NEW变量）必须使用BEFORE触发器，且需要用行级触发FOR EACH ROW，你当前用的AFTER+语句级触发，既无法让修改的NEW值生效，也拿不到单条行的上下文变量。
• 错误使用OLD变量：INSERT操作中没有OLD变量，值为NULL，自然计算出的年龄为空；即使是UPDATE操作，你要基于最新修改后的出生日期计算年龄，也应该用NEW.dob而非OLD存储的旧值。
• 多余的全表查询：不需要从employees表查询数据，触发器行级模式下可以直接通过NEW变量拿到当前行的所有字段值，全表查询在表数据量大于1的时候还会直接报返回行过多的执行错误。

修正后的触发器函数 #

CREATE OR REPLACE function to_age()
RETURNS TRIGGER AS $$
BEGIN
    -- 直接用当前行的dob计算年龄，不需要额外查询
    NEW.age_of_person := date_part('year', age(NEW.dob))::int;
    raise notice 'success(%)', NEW.age_of_person;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

修正后的触发器创建语句 #

-- 先删除旧的错误触发器
DROP TRIGGER IF EXISTS ages on employees;
CREATE TRIGGER ages
    BEFORE INSERT OR UPDATE OF dob ON employees -- 只有dob修改时才触发，优化性能
    FOR EACH ROW
    EXECUTE FUNCTION to_age();

补充说明：如果dob字段为NULL的话计算出的age_of_person也会为NULL，属于正常逻辑，如果需要默认值可以额外加判断处理，示例：NEW.age_of_person := COALESCE(date_part('year', age(NEW.dob))::int, 0);

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