PostgreSQL匹配两表日期取汇率更新采购价的函数报错解决

你之前的语法错误来源于对SQL关联写法的误解：test.rate (date) 这种"表名+空格+括号加字段名"的结构，在SQL解析时会被识别成调用名为test.rate的函数，但实际test.rate是业务表不是函数，因此触发"函数不存在"的报错。
同时原逻辑没有适配PostgreSQL触发器的运行规则，无法自动完成逐行的汇率换算。

1. 先修正原建表语句的语法问题 #

原test.rate表定义的最后一列后多了一个多余逗号，且建议给日期字段加主键约束，避免同一天出现重复汇率值导致关联异常：

-- 修正后的汇率表
CREATE TABLE test.rate
(
    date date NOT NULL PRIMARY KEY,
    rate numeric(10,4) NOT NULL
);

-- 原采购表无需修改即可直接运行
CREATE TABLE test.purchase
(
    prod_id text COLLATE pg_catalog."default",
    import_id text COLLATE pg_catalog."default",
    exw_price_usd numeric(10,2),
    exw_price_eur numeric(10,2),
    import_exp numeric(10,2),
    stock_price numeric(10,2),
    qty numeric(10,0),
    date date NOT NULL
);

2. 编写自动换算的触发器函数 #

函数逻辑会逐行读取当前正在操作的采购记录，匹配同日期的汇率值，自动计算欧元出厂价：

CREATE OR REPLACE FUNCTION test.calc_purchase_eur_price()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅当采购记录的美元价、日期非空时才执行计算
    IF NEW.exw_price_usd IS NOT NULL AND NEW."date" IS NOT NULL THEN
        -- 正确关联同日期汇率：用表别名匹配同字段值，而非函数调用写法
        SELECT r.rate
        INTO NEW.exw_price_eur
        FROM test.rate r
        WHERE r."date" = NEW."date";

        -- 无匹配汇率时给出提示，避免静默错误
        IF NEW.exw_price_eur IS NULL THEN
            RAISE NOTICE '日期%未匹配到有效汇率，暂不计算欧元价格', NEW."date";
        ELSE
            -- 按需求计算：美元价 / 当日汇率，保留2位小数匹配字段精度
            NEW.exw_price_eur := round(NEW.exw_price_usd / NEW.exw_price_eur, 2);
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 给采购表绑定触发器 #

配置为插入、更新美元价/日期字段时自动触发计算逻辑：

CREATE TRIGGER trg_calc_eur_price
BEFORE INSERT OR UPDATE OF exw_price_usd, "date"
ON test.purchase
FOR EACH ROW
EXECUTE FUNCTION test.calc_purchase_eur_price();

按照你给出的测试场景执行：

1. 插入测试汇率：INSERT INTO test.rate VALUES ('2020-10-10', 1.2345);
2. 插入测试采购记录：INSERT INTO test.purchase (prod_id, exw_price_usd, "date") VALUES ('SKU001', 30, '2020-10-10');
3. 查询该采购记录的exw_price_eur字段，会自动计算得到 round(30 / 1.2345, 2) = 24.30，完全匹配换算需求。

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