v3-0001-misc-fix-for-V2-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR.no-cfbot

From d2c367f95c9f3758b31e14c123bd86edfb03fc7e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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