如何创建PostgreSQL触发器实现插入后自动计算列？附SQLite实现

问题说明 #

我尝试在PostgreSQL中每次插入新行后自动填充计算列，但始终无法成功。数据集有数亿行，要求触发器仅处理刚插入的行，不能影响其他数据，性能优先。

原表结构（含语法错误修正） #

原表定义中mem_diff REAL后缺少逗号，先修正：

CREATE TABLE myTable (
    datetime TEXT,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,   -- 插入后计算：上一行的cputil
    mem_lag REAL,    -- 插入后计算：上一行的memfree
    util_diff REAL,  -- 插入后计算：当前cputil - util_lag
    mem_diff REAL,   -- 插入后计算：当前memfree - mem_lag
    util_change TEXT -- 插入后计算：util_diff的变化方向
);

为提升查询性能，建议创建联合索引：

CREATE INDEX idx_mytable_sysid_datetime ON myTable(sys_id, datetime);

错误尝试的问题点 #

之前的写法存在两类问题：

• 混淆SQLite与PostgreSQL触发器语法：PostgreSQL触发器必须通过函数实现，且AFTER INSERT触发器无法直接修改NEW行
• 逻辑错误：用LAG()窗口函数会遍历全表，不符合仅处理当前行的性能要求；函数中存在未闭合的IF语句、变量引用错误

PostgreSQL正确实现方案 #

1. 创建触发器函数 #

使用BEFORE INSERT触发器，在插入前直接计算并填充列值，避免后续UPDATE操作，性能更优：

CREATE OR REPLACE FUNCTION fn_calculate_columns()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
    prev_cputil REAL;
    prev_memfree REAL;
BEGIN
    -- 精准获取当前sys_id的上一行（最近的更早时间记录）
    SELECT cputil, memfree
    INTO prev_cputil, prev_memfree
    FROM myTable
    WHERE sys_id = NEW.sys_id AND datetime < NEW.datetime
    ORDER BY datetime DESC
    LIMIT 1;

    -- 填充滞后值
    NEW.util_lag := prev_cputil;
    NEW.mem_lag := prev_memfree;

    -- 计算差值
    NEW.util_diff := CASE WHEN prev_cputil IS NOT NULL THEN NEW.cputil - prev_cputil ELSE NULL END;
    NEW.mem_diff := CASE WHEN prev_memfree IS NOT NULL THEN NEW.memfree - prev_memfree ELSE NULL END;

    -- 标记变化方向
    IF NEW.util_diff > 0 THEN
        NEW.util_change := 'Up';
    ELSIF NEW.util_diff < 0 THEN
        NEW.util_change := 'Down';
    ELSE
        NEW.util_change := '';
    END IF;

    RETURN NEW;
END;
$$;

2. 创建触发器 #

CREATE TRIGGER tr_fill_calculated_columns
BEFORE INSERT ON myTable
FOR EACH ROW
EXECUTE FUNCTION fn_calculate_columns();

SQLite实现方案 #

SQLite支持直接在触发器中编写逻辑，无需单独定义函数：

1. 创建表及索引 #

CREATE TABLE myTable (
    datetime TEXT,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,
    mem_lag REAL,
    util_diff REAL,
    mem_diff REAL,
    util_change TEXT
);
CREATE INDEX idx_mytable_sysid_datetime ON myTable(sys_id, datetime);

2. 创建触发器（SQLite 3.35.0+推荐用WITH子句减少重复查询） #

CREATE TRIGGER tr_fill_calculated_columns
AFTER INSERT ON myTable
FOR EACH ROW
BEGIN
    WITH prev_data AS (
        SELECT cputil, memfree 
        FROM myTable 
        WHERE sys_id = NEW.sys_id AND datetime < NEW.datetime 
        ORDER BY datetime DESC 
        LIMIT 1
    )
    UPDATE myTable
    SET 
        util_lag = (SELECT cputil FROM prev_data),
        mem_lag = (SELECT memfree FROM prev_data),
        util_diff = CASE WHEN (SELECT cputil FROM prev_data) IS NOT NULL THEN NEW.cputil - (SELECT cputil FROM prev_data) ELSE NULL END,
        mem_diff = CASE WHEN (SELECT memfree FROM prev_data) IS NOT NULL THEN NEW.memfree - (SELECT memfree FROM prev_data) ELSE NULL END,
        util_change = CASE
            WHEN (NEW.cputil - (SELECT cputil FROM prev_data)) > 0 THEN 'Up'
            WHEN (NEW.cputil - (SELECT cputil FROM prev_data)) < 0 THEN 'Down'
            ELSE ''
        END
    WHERE datetime = NEW.datetime AND sys_id = NEW.sys_id;
END;

效果验证 #

插入示例数据后，计算列会自动填充，结果与预期一致：

datetime,sys_id,cputil,memfree,sessnum,util_lag,mem_lag,util_diff,mem_diff,util_change
2019/05/03 08:06:14,100,0.57,0.51,47,,,,
2019/05/03 08:11:14,100,0.47,0.62,43,0.57,0.51,-0.1,0.11,Down
2019/05/03 08:16:14,100,0.56,0.57,62,0.47,0.62,0.09,-0.05,Up
...

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