Postgres触发器插入所有行

Question

有两个表test.purchase和test.stock。在用INSERT查询和该表中的3个计算值触发器填充test.purchase之后，最后一个触发器必须通过INSERT INTO将这些值从test.purchase复制到test.stock中
但每次插入test.purchase之后，触发器都会将test.purchase的所有行插入test.stock
请建议如何在查询INSERT INTO test.purchase后仅插入test.purchase的最后一行

CREATE TABLE test.purchase
(
import_id integer,
product_id integer,
usd_price numeric(10,2),
euro_price numeric(10,2),
qty integer,
euro_stock_price numeric(10,2),
expiry_date date,
euro_stock_amt numeric(10,2),
CONSTRAINT id UNIQUE (product_id)
INCLUDE(import_id),
CONSTRAINT purchase_import_id_fkey FOREIGN KEY (import_id)
REFERENCES test.imports (import_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT purchase_product_id_fkey FOREIGN KEY (product_id)
REFERENCES test.products (product_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
CREATE TRIGGER tr_purchase_euro_after
AFTER INSERT OR UPDATE OF usd_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_price();
CREATE FUNCTION test.fn_purchase_euro_stock_price()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_stock_price = euro_price + euro_price * i.costs_per_euro
FROM test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER tr_purchase_euro_stock_amount
AFTER INSERT OR UPDATE OF euro_stock_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_stock_amt();
CREATE FUNCTION test.fn_purchase_euro_stock_amt()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_stock_amt = euro_stock_price * qty
FROM test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER tr_purchase_euro_stock_price
AFTER INSERT OR UPDATE OF euro_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_stock_price();
CREATE TRIGGER tr_purchase_stock_plus_after_insert
AFTER INSERT
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_stock_plus();
CREATE FUNCTION test.fn_purchase_stock_plus()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
INSERT INTO test.stock(
product_id,
stock_qty, 
stock_price, 
stock_amt,
import_id,
expiry_date)
(SELECT DISTINCT
pr.product_id,
pr.qty,
pr.euro_stock_price,
pr.euro_stock_amt,
pr.import_id,
pr.expiry_date
FROM test.purchase pr
);
RETURN NEW;
END
$BODY$;
CREATE TABLE test.stock
(
product_id integer NOT NULL,
stock_qty integer,
stock_price numeric(10,2),
stock_amt numeric(10,2),
import_id integer,
expiry_date date
)
INSERT INTO test.purchase(
import_id, product_id, usd_price, qty, expiry_date)
VALUES (2,1,35,100, '12-21-2022');
INSERT INTO test.purchase(
import_id, product_id, usd_price, qty, expiry_date)
VALUES (2,2,35,100, '12-21-2022');
INSERT INTO test.purchase(
import_id, product_id, usd_price, qty, expiry_date)
VALUES (2,3,55,100, '12-21-2022');
beautylabs=# SELECT * FROM test.purchase;

>euro_stock_price>td>32.84<2022-12-21>>>>2022-12-21

import_id	product_id	usd_price	euro_price		数量
2	1	35.00	28.60	100	3284.00
2	2	35.00	28.60	100	32.84	2022-121	3284.00
2	3	55.00	44.94	100	51.60	5160.00

cleary在您的proc中，您将在test.purchase表中插入所有内容，如果您想插入新插入/更新的列，则必须使用NEW例如：

CREATE FUNCTION test.fn_purchase_stock_plus()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
INSERT INTO test.stock(
product_id,
stock_qty, 
stock_price, 
stock_amt,
import_id,
expiry_date)
VaLUES(
NEW.product_id,
NEW.qty,
NEW.euro_stock_price,
NEW.euro_stock_price * qty, -- replaced stock_amt
NEW.import_id,
NEW.expiry_date
);
RETURN NEW;
END
$BODY$;

如果这是你想做的