Postgresql使用触发器在插入值后更新列值

Question

我想将值插入到tableA中，然后更新列[amount_hkd]。我已经成功地使用了触发器，但性能确实很慢，在我应用触发器后，需要一个小时才能完成8400行的插入。我怎样才能提高性能?谢谢你的帮助

要执行的语句:

INSERT INTO tableA (suppliers, invoice_dates, shipment_dates, amounts, currency, currency_conversion_rate)
SELECT l.supplier, l.invoice_date, l.shipment_date, l.amount, l.currency, o.currency_conversion_rate
FROM tableB l
LEFT JOIN tableC o
ON l.currency = o.currency_code
WHERE l.supplier = 'ABC'

我创建的函数:

CREATE OR REPLACE FUNCTION cal() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE tableA 
SET amount_hkd = NEW.amounts * NEW.currency_conversion_rate;
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

The first Trigger I try:

CREATE CONSTRAINT TRIGGER update_amount
AFTER INSERT ON tableA
DEFERRABLE
INITIALLY DEFERRED
FOR EACH ROW 
EXECUTE PROCEDURE cal();

The second Trigger I tried:

CREATE TRIGGER update_amount 
AFTER INSERT ON tableA
FOR EACH ROW 
EXECUTE PROCEDURE cal();

Answer 1

在插入行之后更新该行的效率非常低。更好的方法是使用BEFORE触发器，它可以在插入之前修改新行:

CREATE OR REPLACE FUNCTION cal() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN 
NEW.amount_hkd := NEW.amounts * NEW.currency_conversion_rate;
RETURN NEW; 
END;$$;
CREATE TRIGGER update_amount 
BEFORE INSERT ON tableA FOR EACH ROW 
EXECUTE PROCEDURE cal();

Answer 2

您没有指定您的Postgres版本，但如果至少V12，您可以完全避免触发器。将列amount_hkd定义为生成的列。您不仅可以避免触发，还可以消除更新currency_conversion_rate和/或amount而忘记更新amount_hkd或直接更新amount_hkd的维护问题。比如:(见这里的例子)

create table table_a( a_id integer generated always as identity 
, amounts  decimal
, currency_conversion_rate decimal
, amount_hkd decimal generated always as ( amounts * currency_conversion_rate ) stored
);