Postgresql using Trigger to update column values after inserting values

Question

I want to insert the values into a tableA and then update the column[amount_hkd]. I have successfully use the trigger but the performance is really slow, it takes an hour to finish the insertion of 8400 rows after I apply the trigger. How can I improve the performance? Thanks for your help

The statement to exectue:

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'

The function I created:

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 1st Trigger I tried:

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

The 2nd Trigger I tried:

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

Answer 1

It is terribly inefficient to update the row after you inserted it. Better is to use a BEFORE trigger that can modify the new row before it is inserted:

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();