v51-0004-Expose-FOR-PORTION-OF-to-plpgsql-triggers.patch

From f091addda6f75acbf94922765ceaf5316bd544d2 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 29 Oct 2024 18:54:37 -0700
Subject: [PATCH v51 4/5] Expose FOR PORTION OF to plpgsql triggers

It is helpful for triggers to see what the FOR PORTION OF clause
specified: both the column/period name and the targeted bounds. Our RI
triggers require this information, and we are passing it as part of the
TriggerData struct. This commit allows plpgsql triggers functions to
access the same information, using the new TG_PORTION_COLUMN and
TG_PORTION_TARGET variables.

Author: Paul Jungwirth
---
 doc/src/sgml/plpgsql.sgml                    | 24 +++++++++++
 src/pl/plpgsql/src/pl_comp.c                 | 27 +++++++++++++
 src/pl/plpgsql/src/pl_exec.c                 | 32 +++++++++++++++
 src/pl/plpgsql/src/plpgsql.h                 |  2 +
 src/test/regress/expected/for_portion_of.out | 42 ++++++++++----------
 src/test/regress/sql/for_portion_of.sql      | 14 ++++---
 6 files changed, 115 insertions(+), 26 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index e937491e6b8..f5199872e2e 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -4247,6 +4247,30 @@ ASSERT <replaceable class="parameter">condition</replaceable> <optional> , <repl
       </para>
      </listitem>
     </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-temporal-column">
+     <term><varname>TG_PERIOD_NAME</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       the column name used in a <literal>FOR PORTION OF</literal> clause,
+       or else <symbol>NULL</symbol>.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry id="plpgsql-dml-trigger-tg-temporal-target">
+     <term><varname>TG_PERIOD_BOUNDS</varname> <type>text</type></term>
+     <listitem>
+      <para>
+       the range/multirange/etc. given as the bounds of a
+       <literal>FOR PORTION OF</literal> clause, either directly (with parens syntax)
+       or computed from the <literal>FROM</literal> and <literal>TO</literal> bounds.
+       <symbol>NULL</symbol> if <literal>FOR PORTION OF</literal> was not used.
+       This is a text value based on the type's output function,
+       since the type can't be known at function creation time.
+      </para>
+     </listitem>
+    </varlistentry>
    </variablelist>
   </para>
 
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 519f7695d7c..b716703b320 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -608,6 +608,33 @@ plpgsql_compile_callback(FunctionCallInfo fcinfo,
 			var->dtype = PLPGSQL_DTYPE_PROMISE;
 			((PLpgSQL_var *) var)->promise = PLPGSQL_PROMISE_TG_ARGV;
 
+			/* Add the variable tg_period_name */
+			var = plpgsql_build_variable("tg_period_name", 0,
+										 plpgsql_build_datatype(TEXTOID,
+																-1,
+																function->fn_input_collation,
+																NULL),
+										 true);
+			Assert(var->dtype == PLPGSQL_DTYPE_VAR);
+			var->dtype = PLPGSQL_DTYPE_PROMISE;
+			((PLpgSQL_var *) var)->promise = PLPGSQL_PROMISE_TG_PERIOD_NAME;
+
+			/*
+			 * Add the variable to tg_period_bounds.
+			 * This could be any rangetype or multirangetype
+			 * or user-supplied type,
+			 * so the best we can offer is a TEXT variable.
+			 */
+			var = plpgsql_build_variable("tg_period_bounds", 0,
+										 plpgsql_build_datatype(TEXTOID,
+																-1,
+																function->fn_input_collation,
+																NULL),
+										 true);
+			Assert(var->dtype == PLPGSQL_DTYPE_VAR);
+			var->dtype = PLPGSQL_DTYPE_PROMISE;
+			((PLpgSQL_var *) var)->promise = PLPGSQL_PROMISE_TG_PERIOD_BOUNDS;
+
 			break;
 
 		case PLPGSQL_EVENT_TRIGGER:
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index bb99781c56e..850066aa31c 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -1385,6 +1385,7 @@ plpgsql_fulfill_promise(PLpgSQL_execstate *estate,
 						PLpgSQL_var *var)
 {
 	MemoryContext oldcontext;
+	ForPortionOfState *fpo;
 
 	if (var->promise == PLPGSQL_PROMISE_NONE)
 		return;					/* nothing to do */
@@ -1516,6 +1517,37 @@ plpgsql_fulfill_promise(PLpgSQL_execstate *estate,
 			}
 			break;
 
+		case PLPGSQL_PROMISE_TG_PERIOD_NAME:
+			if (estate->trigdata == NULL)
+				elog(ERROR, "trigger promise is not in a trigger function");
+			if (estate->trigdata->tg_temporal)
+				assign_text_var(estate, var, estate->trigdata->tg_temporal->fp_rangeName);
+			else
+				assign_simple_var(estate, var, (Datum) 0, true, false);
+			break;
+
+		case PLPGSQL_PROMISE_TG_PERIOD_BOUNDS:
+			fpo = estate->trigdata->tg_temporal;
+
+			if (estate->trigdata == NULL)
+				elog(ERROR, "trigger promise is not in a trigger function");
+			if (fpo)
+			{
+
+				Oid		funcid;
+				bool	varlena;
+
+				getTypeOutputInfo(fpo->fp_rangeType, &funcid, &varlena);
+				Assert(OidIsValid(funcid));
+
+				assign_text_var(estate, var,
+								OidOutputFunctionCall(funcid,
+													  fpo->fp_targetRange));
+			}
+			else
+				assign_simple_var(estate, var, (Datum) 0, true, false);
+			break;
+
 		case PLPGSQL_PROMISE_TG_EVENT:
 			if (estate->evtrigdata == NULL)
 				elog(ERROR, "event trigger promise is not in an event trigger function");
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 41e52b8ce71..4f15fef6c9b 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -85,6 +85,8 @@ typedef enum PLpgSQL_promise_type
 	PLPGSQL_PROMISE_TG_ARGV,
 	PLPGSQL_PROMISE_TG_EVENT,
 	PLPGSQL_PROMISE_TG_TAG,
+	PLPGSQL_PROMISE_TG_PERIOD_NAME,
+	PLPGSQL_PROMISE_TG_PERIOD_BOUNDS,
 } PLpgSQL_promise_type;
 
 /*
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 19a4e9ac523..4f9ee28f078 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -512,16 +512,18 @@ RETURNS TRIGGER LANGUAGE plpgsql AS
 $$
 BEGIN
   IF TG_OP = 'INSERT' THEN
-    RAISE NOTICE '%: % %, NEW table = %',
-      TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table);
+    RAISE NOTICE '%: % FOR PORTION OF % (%) %, NEW table = %',
+      TG_NAME, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL,
+      (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table);
   ELSIF TG_OP = 'UPDATE' THEN
-    RAISE NOTICE '%: % %, OLD table = %, NEW table = %',
-      TG_NAME, TG_OP, TG_LEVEL,
+    RAISE NOTICE '%: % FOR PORTION OF % (%) %, OLD table = %, NEW table = %',
+      TG_NAME, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL,
       (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table),
       (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table);
   ELSIF TG_OP = 'DELETE' THEN
-    RAISE NOTICE '%: % %, OLD table = %',
-      TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table);
+    RAISE NOTICE '%: % FOR PORTION OF % (%) %, OLD table = %',
+      TG_NAME, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL,
+      (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table);
   END IF;
   RETURN NULL;
 END;
@@ -554,29 +556,29 @@ BEGIN;
 UPDATE for_portion_of_test
   FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01'
   SET name = '2018-01-15_to_2019-01-01';
-NOTICE:  for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one)
-NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one)
-NOTICE:  for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one)
-NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one)
-NOTICE:  for_portion_of_test_update_trig: UPDATE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01)
-NOTICE:  for_portion_of_test_update_trig_stmt: UPDATE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01)
+NOTICE:  for_portion_of_test_insert_trig: INSERT FOR PORTION OF <NULL> (<NULL>) ROW, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one)
+NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF <NULL> (<NULL>) STATEMENT, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one)
+NOTICE:  for_portion_of_test_insert_trig: INSERT FOR PORTION OF <NULL> (<NULL>) ROW, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one)
+NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF <NULL> (<NULL>) STATEMENT, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one)
+NOTICE:  for_portion_of_test_update_trig: UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01)
+NOTICE:  for_portion_of_test_update_trig_stmt: UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01)
 ROLLBACK;
 BEGIN;
 DELETE FROM for_portion_of_test
   FOR PORTION OF valid_at FROM NULL TO '2018-01-21';
-NOTICE:  for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one)
-NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one)
-NOTICE:  for_portion_of_test_delete_trig: DELETE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one)
-NOTICE:  for_portion_of_test_delete_trig_stmt: DELETE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE:  for_portion_of_test_insert_trig: INSERT FOR PORTION OF <NULL> (<NULL>) ROW, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one)
+NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF <NULL> (<NULL>) STATEMENT, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one)
+NOTICE:  for_portion_of_test_delete_trig: DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one)
+NOTICE:  for_portion_of_test_delete_trig_stmt: DELETE FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one)
 ROLLBACK;
 BEGIN;
 UPDATE for_portion_of_test
   FOR PORTION OF valid_at FROM NULL TO '2018-01-02'
   SET name = 'NULL_to_2018-01-01';
-NOTICE:  for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one)
-NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one)
-NOTICE:  for_portion_of_test_update_trig: UPDATE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01)
-NOTICE:  for_portion_of_test_update_trig_stmt: UPDATE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01)
+NOTICE:  for_portion_of_test_insert_trig: INSERT FOR PORTION OF <NULL> (<NULL>) ROW, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one)
+NOTICE:  for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF <NULL> (<NULL>) STATEMENT, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one)
+NOTICE:  for_portion_of_test_update_trig: UPDATE FOR PORTION OF valid_at ((,2018-01-02)) ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01)
+NOTICE:  for_portion_of_test_update_trig_stmt: UPDATE FOR PORTION OF valid_at ((,2018-01-02)) STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01)
 ROLLBACK;
 -- Test with multiranges
 CREATE TABLE for_portion_of_test2 (
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index e66a55fe014..cd1645a436f 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -396,16 +396,18 @@ RETURNS TRIGGER LANGUAGE plpgsql AS
 $$
 BEGIN
   IF TG_OP = 'INSERT' THEN
-    RAISE NOTICE '%: % %, NEW table = %',
-      TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table);
+    RAISE NOTICE '%: % FOR PORTION OF % (%) %, NEW table = %',
+      TG_NAME, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL,
+      (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table);
   ELSIF TG_OP = 'UPDATE' THEN
-    RAISE NOTICE '%: % %, OLD table = %, NEW table = %',
-      TG_NAME, TG_OP, TG_LEVEL,
+    RAISE NOTICE '%: % FOR PORTION OF % (%) %, OLD table = %, NEW table = %',
+      TG_NAME, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL,
       (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table),
       (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table);
   ELSIF TG_OP = 'DELETE' THEN
-    RAISE NOTICE '%: % %, OLD table = %',
-      TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table);
+    RAISE NOTICE '%: % FOR PORTION OF % (%) %, OLD table = %',
+      TG_NAME, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL,
+      (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table);
   END IF;
   RETURN NULL;
 END;
-- 
2.39.5