On Sat, Dec 28, 2019 at 12:12:30AM -0300, Alvaro Herrera wrote:
> On 2019-Dec-28, David Fetter wrote:
>
> > While noodling around with an upcoming patch to remove user-modifiable
> > RULEs, I noticed that WHEN conditions were disallowed from INSTEAD OF
> > triggers for no discernible reason. This patch removes that
> > restriction.
>
> If you want to remove the restriction, your patch should add some test
> cases that show it working.
Tests added :)
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
>From d6af8b66347b31e14d961e83023dbcb658bea64b Mon Sep 17 00:00:00 2001
From: David Fetter <da...@fetter.org>
Date: Fri, 27 Dec 2019 18:57:31 -0800
Subject: [PATCH v2] Allow WHEN in INSTEAD OF triggers
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.24.1"
This is a multi-part message in MIME format.
--------------2.24.1
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit
This was disallowed for reasons that aren't entirely obvious, so allow.
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 3339a4b4e1..b822cb6e8d 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -377,10 +377,6 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>.
</para>
- <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
- conditions.
- </para>
-
<para>
Currently, <literal>WHEN</literal> expressions cannot contain
subqueries.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 36093a29a8..c4cc07a426 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -381,17 +381,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("TRUNCATE FOR EACH ROW triggers are not supported")));
- /* INSTEAD triggers must be row-level, and can't have WHEN or columns */
+ /* INSTEAD triggers must be row-level, and can't have columns */
if (TRIGGER_FOR_INSTEAD(tgtype))
{
if (!TRIGGER_FOR_ROW(tgtype))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("INSTEAD OF triggers must be FOR EACH ROW")));
- if (stmt->whenClause)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("INSTEAD OF triggers cannot have WHEN conditions")));
if (stmt->columns != NIL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1e4053ceed..f44f28760e 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -965,7 +965,11 @@ begin
end if;
if TG_OP = 'UPDATE' then
- raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
+ if strpos(argstr, 'instead_of_when') > 0 then
+ raise NOTICE 'instead_of_when fired';
+ else
+ raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
+ end if;
UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;
if NOT FOUND then RETURN NULL; end if;
RETURN NEW;
@@ -1030,10 +1034,6 @@ CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
ERROR: "main_table" is a table
DETAIL: Tables cannot have INSTEAD OF triggers.
--- Don't support WHEN clauses with INSTEAD OF triggers
-CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
-FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd');
-ERROR: INSTEAD OF triggers cannot have WHEN conditions
-- Don't support column-level INSTEAD OF triggers
CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
@@ -1049,6 +1049,9 @@ CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TRIGGER when_different_update INSTEAD OF UPDATE ON main_view
+FOR EACH ROW WHEN (OLD.a IS DISTINCT FROM NEW.a)
+EXECUTE PROCEDURE view_trigger('instead_of_when');
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1145,18 +1148,47 @@ UPDATE 1
UPDATE main_view SET b = 0 WHERE false;
NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
+UPDATE 0
+-- INSTEAD OF ... WHEN trigger fires.
+UPDATE main_view SET a = 23 WHERE a = 21 RETURNING *;
+NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
+NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd)
+NOTICE: OLD: (21,10), NEW: (23,10)
+NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_when)
+NOTICE: instead_of_when fired
+NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd)
+NOTICE: OLD: (21,32), NEW: (23,32)
+NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_when)
+NOTICE: instead_of_when fired
+NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
+ a | b
+---+---
+(0 rows)
+
UPDATE 0
-- Delete from view using trigger
DELETE FROM main_view WHERE a IN (20,21);
NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
-NOTICE: OLD: (21,10)
-NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
NOTICE: OLD: (20,31)
-NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
-NOTICE: OLD: (21,32)
NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt)
-DELETE 3
+DELETE 1
DELETE FROM main_view WHERE a = 31 RETURNING a, b;
NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
@@ -1186,6 +1218,7 @@ Triggers:
instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_del')
instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_ins')
instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd')
+ when_different_update INSTEAD OF UPDATE ON main_view FOR EACH ROW WHEN (old.a IS DISTINCT FROM new.a) EXECUTE FUNCTION view_trigger('instead_of_when')
-- Test dropping view triggers
DROP TRIGGER instead_of_insert_trig ON main_view;
@@ -1208,6 +1241,7 @@ Triggers:
before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_ins_stmt')
before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('before_view_upd_stmt')
instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE FUNCTION view_trigger('instead_of_upd')
+ when_different_update INSTEAD OF UPDATE ON main_view FOR EACH ROW WHEN (old.a IS DISTINCT FROM new.a) EXECUTE FUNCTION view_trigger('instead_of_when')
DROP VIEW main_view;
--
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index c21b6c124e..2e2583b0bc 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -672,7 +672,11 @@ begin
end if;
if TG_OP = 'UPDATE' then
- raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
+ if strpos(argstr, 'instead_of_when') > 0 then
+ raise NOTICE 'instead_of_when fired';
+ else
+ raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
+ end if;
UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;
if NOT FOUND then RETURN NULL; end if;
RETURN NEW;
@@ -727,10 +731,6 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
--- Don't support WHEN clauses with INSTEAD OF triggers
-CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
-FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd');
-
-- Don't support column-level INSTEAD OF triggers
CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
@@ -749,6 +749,10 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TRIGGER when_different_update INSTEAD OF UPDATE ON main_view
+FOR EACH ROW WHEN (OLD.a IS DISTINCT FROM NEW.a)
+EXECUTE PROCEDURE view_trigger('instead_of_when');
+
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -787,6 +791,9 @@ UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
-- Before and after stmt triggers should fire even when no rows are affected
UPDATE main_view SET b = 0 WHERE false;
+-- INSTEAD OF ... WHEN trigger fires.
+UPDATE main_view SET a = 23 WHERE a = 21 RETURNING *;
+
-- Delete from view using trigger
DELETE FROM main_view WHERE a IN (20,21);
DELETE FROM main_view WHERE a = 31 RETURNING a, b;
--------------2.24.1--
