From d2c367f95c9f3758b31e14c123bd86edfb03fc7e Mon Sep 17 00:00:00 2001 From: jian he Date: Tue, 21 Apr 2026 14:21:01 +0800 Subject: [PATCH v3 1/1] misc fix for V2-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR-PORTION-OF Discussion: https://postgr.es/m/CAHg%2BQDd74fnd4obCRMqVS0AVWf%3DcSFH%3DCv7trTJWgm%2B_bhTK6w%40mail.gmail.com Discussion: https://postgr.es/m/CA+renyW9o=-DBK6YO7a31xbr6xw7RFN_tSxTiUi2tSOQ2w5_zQ@mail.gmail.com --- src/test/regress/expected/for_portion_of.out | 16 +++++++++++++--- src/test/regress/sql/for_portion_of.sql | 14 +++++++++++--- 2 files changed, 24 insertions(+), 6 deletions(-) diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out index 1afa26c86bc..47719fc40ea 100644 --- a/src/test/regress/expected/for_portion_of.out +++ b/src/test/regress/expected/for_portion_of.out @@ -2103,15 +2103,24 @@ INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100); CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base; CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN + if TG_OP = 'UPDATE' then + raise NOTICE 'UPDATE OLD: %, NEW: %', OLD, NEW; + RETURN NEW; + elsif TG_OP = 'INSERT' then + raise NOTICE 'INSERT NEW: %', NEW; + RETURN NEW; + elsif TG_OP = 'DELETE' then + raise NOTICE 'DELETE: OLD: %', OLD; + RETURN OLD; + end if; RETURN NEW; END; $$; -CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view - FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn(); -CREATE TRIGGER fpo_instead_del_trig INSTEAD OF DELETE ON fpo_instead_view +CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE OR DELETE ON fpo_instead_view FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn(); UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01' SET val = 999 WHERE id = 1; +NOTICE: UPDATE OLD: (1,"[2024-01-01,2024-12-31)",100), NEW: (1,"[2024-01-01,2024-12-31)",999) SELECT * FROM fpo_instead_view; id | valid_at | val ----+-------------------------+----- @@ -2120,6 +2129,7 @@ SELECT * FROM fpo_instead_view; DELETE FROM fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01' WHERE id = 1; +NOTICE: DELETE: OLD: (1,"[2024-01-01,2024-12-31)",100) SELECT * FROM fpo_instead_view; id | valid_at | val ----+-------------------------+----- diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql index 0b5a86408b9..83082f8739a 100644 --- a/src/test/regress/sql/for_portion_of.sql +++ b/src/test/regress/sql/for_portion_of.sql @@ -1371,12 +1371,20 @@ INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100); CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base; CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN + if TG_OP = 'UPDATE' then + raise NOTICE 'UPDATE OLD: %, NEW: %', OLD, NEW; + RETURN NEW; + elsif TG_OP = 'INSERT' then + raise NOTICE 'INSERT NEW: %', NEW; + RETURN NEW; + elsif TG_OP = 'DELETE' then + raise NOTICE 'DELETE: OLD: %', OLD; + RETURN OLD; + end if; RETURN NEW; END; $$; -CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view - FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn(); -CREATE TRIGGER fpo_instead_del_trig INSTEAD OF DELETE ON fpo_instead_view +CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE OR DELETE ON fpo_instead_view FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn(); UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01' -- 2.34.1