From 4a03f935b3438de27ee00d9e562ffe4e225978a9 Mon Sep 17 00:00:00 2001 From: Kevin Grittner Date: Tue, 16 May 2017 16:09:55 -0500 Subject: [PATCH] Add a test for transition table usage in FOR EACH ROW trigger. --- src/test/regress/expected/sanity_check.out | 1 + src/test/regress/expected/triggers.out | 27 ++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 25 ++++++++++++++++++++ 3 files changed, 53 insertions(+) diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 6750152e0f..6d20f3ec0c 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -191,6 +191,7 @@ timestamp_tbl|f timestamptz_tbl|f timetz_tbl|f tinterval_tbl|f +transition_table_for_rows|f varchar_tbl|f -- restore normal output mode \a\t diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 0d560fb3ee..8a908f27b3 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1893,3 +1893,30 @@ copy parted_stmt_trig1(a) from stdin; NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW drop table parted_stmt_trig, parted2_stmt_trig; +-- +-- Verify that transition table in FOR EACH ROW trigge shows all rows affected +-- by the statement (through the end) for each row (from the beginning). +-- +create table transition_table_for_rows (c int not null); +create function transition_table_for_rows_func() + returns trigger + language plpgsql +as $$ +begin + raise notice '% / % = %', + new.c, + (select sum(c) from newtable), + (select new.c::float / sum(newtable.c) from newtable); + return null; +end; +$$; +create trigger transition_table_for_rows_trig + after insert or update on transition_table_for_rows + referencing new table as newtable + for each row + execute procedure transition_table_for_rows_func(); +insert into transition_table_for_rows select generate_series(1,4); +NOTICE: 1 / 10 = 0.1 +NOTICE: 2 / 10 = 0.2 +NOTICE: 3 / 10 = 0.3 +NOTICE: 4 / 10 = 0.4 diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 5581fcb164..7231b771a4 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1360,3 +1360,28 @@ copy parted_stmt_trig1(a) from stdin; \. drop table parted_stmt_trig, parted2_stmt_trig; + +-- +-- Verify that transition table in FOR EACH ROW trigger shows all rows +-- affected by the statement (through the end) for each row (from the +-- beginning). +-- +create table transition_table_for_rows (c int not null); +create function transition_table_for_rows_func() + returns trigger + language plpgsql +as $$ +begin + raise notice '% / % = %', + new.c, + (select sum(c) from newtable), + (select new.c::float / sum(newtable.c) from newtable); + return null; +end; +$$; +create trigger transition_table_for_rows_trig + after insert or update on transition_table_for_rows + referencing new table as newtable + for each row + execute procedure transition_table_for_rows_func(); +insert into transition_table_for_rows select generate_series(1,4); -- 2.40.0