如何用PL/pgSQL函数与触发器关联3表计算PRODUCTS_BOUGHT的COST列

原代码的核心问题 #

1. 触发器函数错误使用全表查询而非当前行数据：应该用NEW对象访问当前插入/更新的行字段，而非直接查询products_bought表
2. 关联条件完全无效：customers_register.type = customers_register.type是无意义的自连接，未关联客户表与购买表的客户ID，也未关联价格表的类型与客户类型
3. PRICE字段带$符号为字符串类型，需转换为数值才能参与计算
4. 添加cost列时未指定数据类型，且触发器函数错误返回cost而非NEW记录

正确实现步骤 #

1. 添加指定类型的COST列

ALTER TABLE products_bought
ADD COLUMN cost NUMERIC; -- 明确指定数值类型，适配计算结果

2. 创建正确的触发器函数

CREATE OR REPLACE FUNCTION calc_cost()
RETURNS TRIGGER AS $$
BEGIN
  -- 关联三张表获取对应单价，计算后赋值给当前行的cost字段
  SELECT (NEW.products * REPLACE(pp.price, '$', '')::NUMERIC)
  INTO NEW.cost
  FROM customers_register cr
  JOIN products_price pp 
    ON cr.type = pp.type 
    AND pp.date = NEW.date
  WHERE cr.customer = NEW.customer;
  
  RETURN NEW; -- 触发器必须返回NEW/OLD记录，否则无法完成插入/更新
END;
$$ LANGUAGE plpgsql;

3. 创建触发器

CREATE OR REPLACE TRIGGER cost_trigger
BEFORE INSERT OR UPDATE ON products_bought
FOR EACH ROW
EXECUTE FUNCTION calc_cost();

4. 更新已有数据（触发器仅处理新增/更新的行）

UPDATE products_bought pb
SET cost = pb.products * REPLACE(pp.price, '$', '')::NUMERIC
FROM customers_register cr
JOIN products_price pp 
  ON cr.type = pp.type 
  AND pp.date = pb.date
WHERE cr.customer = pb.customer;

关键说明 #

• NEW对象的使用：触发器中NEW代表即将插入/更新的行，当前行的所有字段需通过NEW.字段名访问
• 价格字段处理：用REPLACE(pp.price, '$', '')::NUMERIC去除$符号并转换为数值类型，才能进行乘法运算
• 关联逻辑：通过客户ID关联CUSTOMERS_REGISTER与PRODUCTS_BOUGHT，再通过客户类型+日期关联PRODUCTS_PRICE，确保匹配到对应单价
• 触发器返回值：BEFORE类型的触发器必须返回NEW记录，否则插入/更新操作会失败

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