如何创建触发器计算两张表间价格差值并写入result表

需求说明 #

当向table2中插入数据时，检查table1中是否存在相同的id，获取对应的start_price，按公式计算价格差值百分比，将结果插入result表；若result表中已存在该id，则更新对应结果值。

示例 #

假设table1已有数据，执行插入操作：

INSERT INTO table2 VALUES (1, 135);

result表中应得到：

1 3.58%

计算逻辑：100 - ((135/140) * 100) = 3.58%（其中140是table1中id=1的start_price）

表结构 #

CREATE TABLE table1
(
  id integer NOT NULL,
  start_price integer NOT NULL,
  CONSTRAINT table1_pkey PRIMARY KEY (id)
);

CREATE TABLE table2
(
  id integer NOT NULL,
  end_price integer NOT NULL
);

CREATE TABLE result
(
  id integer NOT NULL,
  result_price decimal NOT NULL
);

-- 初始化table1数据
INSERT INTO table1 VALUES (1, 140);
INSERT INTO table1 VALUES (2, 230);
INSERT INTO table1 VALUES (3, 70);

初始未完成的触发器代码 #

CREATE OR REPLACE FUNCTION delta_percent_calc() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO
        result(id,name)
        -- 此处需补充逻辑，考虑table1和table2价格更新的情况

           RETURN new;
END;
$BODY$
language plpgsql;

可行解决方案 #

以下是完善后的实现，解决了整数除法精度问题，同时处理了result表中id冲突时的更新逻辑：

触发器函数 #

CREATE OR REPLACE FUNCTION delta_percent_calc() RETURNS TRIGGER AS
$BODY$
DECLARE
        s_price integer;
BEGIN
    -- 从table1获取对应id的start_price
    select start_price into s_price from table1 where id = new.id;
    
    -- 插入或更新result表
    INSERT INTO 
        result (id,result_price)
          VALUES(new.id, round(100 - ( (new.end_price::numeric/s_price::numeric) * 100), 4 ) );
          ON CONFLICT (id) DO UPDATE
          SET result_price = round(100 - ( (new.end_price::numeric/s_price::numeric) * 100), 4 );

    RETURN new;
END;
$BODY$
language plpgsql;

创建触发器 #

CREATE OR REPLACE TRIGGER trig_percent_calc
     AFTER INSERT ON table2
     FOR EACH ROW
     EXECUTE PROCEDURE delta_percent_calc();

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