From 3dcca6fdbaf1b07dd1bd40bc6149055e5da58528 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 29 Oct 2024 18:54:37 -0700 Subject: [PATCH v46 7/8] 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. --- 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 78e4983139b..31bd824ed3d 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -4245,6 +4245,30 @@ ASSERT condition , + + + TG_PERIOD_NAME text + + + the column name used in a FOR PORTION OF clause, + or else NULL. + + + + + + TG_PERIOD_BOUNDS text + + + the range/multirange/etc. given as the bounds of a + FOR PORTION OF clause, either directly (with parens syntax) + or computed from the FROM and TO bounds. + NULL if FOR PORTION OF 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. + + + diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index ab215885222..0eefa4e77f9 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -717,6 +717,33 @@ do_compile(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 e5b0da04e3c..37184224503 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -1369,6 +1369,7 @@ plpgsql_fulfill_promise(PLpgSQL_execstate *estate, PLpgSQL_var *var) { MemoryContext oldcontext; + ForPortionOfState *fpo; if (var->promise == PLPGSQL_PROMISE_NONE) return; /* nothing to do */ @@ -1500,6 +1501,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 d00b9ce0da2..a83c5f24ce0 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -84,6 +84,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 () ROW, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF () STATEMENT, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: for_portion_of_test_insert_trig: INSERT FOR PORTION OF () ROW, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF () 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 () ROW, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF () 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 () ROW, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT FOR PORTION OF () 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.42.0