From 5e5fb60270f280facbd887db11bebfb9df61802b Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 29 Oct 2024 18:54:37 -0700 Subject: [PATCH v59 10/11] 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 trigger functions to access the same information, using the new TG_PERIOD_COLUMN and TG_PERIOD_TARGET variables. Author: Paul A. Jungwirth --- doc/src/sgml/plpgsql.sgml | 24 +++++++++ src/pl/plpgsql/src/pl_comp.c | 26 +++++++++ src/pl/plpgsql/src/pl_exec.c | 32 ++++++++++++ src/pl/plpgsql/src/plpgsql.h | 2 + src/test/regress/expected/for_portion_of.out | 55 +++++++++++--------- src/test/regress/sql/for_portion_of.sql | 9 +++- 6 files changed, 121 insertions(+), 27 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 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 f6976689a69..59776358a65 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -617,6 +617,32 @@ 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 d19425b7a71..11eb5a60a79 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 5f193a37183..8000104bc89 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 9288b4224f7..52fb044d2e5 100644 --- a/src/test/regress/expected/for_portion_of.out +++ b/src/test/regress/expected/for_portion_of.out @@ -613,8 +613,13 @@ CREATE FUNCTION dump_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN - RAISE NOTICE '%: % % %:', - TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + IF TG_PERIOD_NAME IS NOT NULL THEN + RAISE NOTICE '%: % % FOR PORTION OF % (%) %:', + TG_NAME, TG_WHEN, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL; + ELSE + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + END IF; IF TG_ARGV[0] THEN RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); @@ -664,10 +669,10 @@ UPDATE for_portion_of_test FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' SET name = 'five^3' WHERE id = '[5,6)'; -NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: fpo_before_stmt: BEFORE UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: fpo_before_row: BEFORE UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: [2019-01-01,2030-01-01) NOTICE: new: [2021-01-01,2022-01-01) NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: @@ -694,19 +699,19 @@ NOTICE: new: [2022-01-01,2030-01-01) NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) ROW: NOTICE: old: [2019-01-01,2030-01-01) NOTICE: new: [2021-01-01,2022-01-01) -NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: fpo_after_update_stmt: AFTER UPDATE FOR PORTION OF valid_at ([2021-01-01,2022-01-01)) STATEMENT: NOTICE: old: NOTICE: new: DELETE FROM for_portion_of_test FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' WHERE id = '[5,6)'; -NOTICE: fpo_before_stmt: BEFORE DELETE STATEMENT: +NOTICE: fpo_before_stmt: BEFORE DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: fpo_before_row: BEFORE DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: [2022-01-01,2030-01-01) NOTICE: new: NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: @@ -733,10 +738,10 @@ NOTICE: new: [2024-01-01,2030-01-01) NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) ROW: NOTICE: old: [2022-01-01,2030-01-01) NOTICE: new: -NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: fpo_after_delete_stmt: AFTER DELETE FOR PORTION OF valid_at ([2023-01-01,2024-01-01)) STATEMENT: NOTICE: old: NOTICE: new: SELECT * FROM for_portion_of_test ORDER BY id, valid_at; @@ -802,10 +807,10 @@ 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: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: fpo_before_stmt: BEFORE UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: fpo_before_row: BEFORE UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: [2018-01-01,2020-01-01) NOTICE: new: [2018-01-15,2019-01-01) NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: @@ -832,20 +837,20 @@ NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: NOTICE: old: NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) -NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: fpo_after_update_stmt: AFTER UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) STATEMENT: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: ("[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: fpo_before_stmt: BEFORE DELETE STATEMENT: +NOTICE: fpo_before_stmt: BEFORE DELETE FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: fpo_before_row: BEFORE DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: [2018-01-01,2020-01-01) NOTICE: new: NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: @@ -860,10 +865,10 @@ NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: NOTICE: old: NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: -NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: fpo_after_delete_stmt: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) STATEMENT: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: ROLLBACK; @@ -871,10 +876,10 @@ 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: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: fpo_before_stmt: BEFORE UPDATE FOR PORTION OF valid_at ((,2018-01-02)) STATEMENT: NOTICE: old: NOTICE: new: -NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: fpo_before_row: BEFORE UPDATE FOR PORTION OF valid_at ((,2018-01-02)) ROW: NOTICE: old: [2018-01-01,2020-01-01) NOTICE: new: [2018-01-01,2018-01-02) NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: @@ -889,10 +894,10 @@ NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: NOTICE: old: NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ((,2018-01-02)) ROW: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) -NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: fpo_after_update_stmt: AFTER UPDATE FOR PORTION OF valid_at ((,2018-01-02)) STATEMENT: NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) ROLLBACK; @@ -929,7 +934,7 @@ NOTICE: new: [2018-01-01,2018-01-15) NOTICE: fpo_after_insert_row: AFTER INSERT ROW: NOTICE: old: NOTICE: new: [2019-01-01,2020-01-01) -NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: fpo_after_update_row: AFTER UPDATE FOR PORTION OF valid_at ([2018-01-15,2019-01-01)) ROW: NOTICE: old: [2018-01-01,2020-01-01) NOTICE: new: [2018-01-15,2019-01-01) BEGIN; @@ -939,10 +944,10 @@ COMMIT; NOTICE: fpo_after_insert_row: AFTER INSERT ROW: NOTICE: old: NOTICE: new: [2018-01-21,2019-01-01) -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: [2018-01-15,2019-01-01) NOTICE: new: -NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: fpo_after_delete_row: AFTER DELETE FOR PORTION OF valid_at ((,2018-01-21)) ROW: NOTICE: old: [2018-01-01,2018-01-15) NOTICE: new: BEGIN; diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql index 0e6c2db5a75..7493cc4c233 100644 --- a/src/test/regress/sql/for_portion_of.sql +++ b/src/test/regress/sql/for_portion_of.sql @@ -484,8 +484,13 @@ CREATE FUNCTION dump_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN - RAISE NOTICE '%: % % %:', - TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + IF TG_PERIOD_NAME IS NOT NULL THEN + RAISE NOTICE '%: % % FOR PORTION OF % (%) %:', + TG_NAME, TG_WHEN, TG_OP, TG_PERIOD_NAME, TG_PERIOD_BOUNDS, TG_LEVEL; + ELSE + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + END IF; IF TG_ARGV[0] THEN RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); -- 2.39.5