Folks,
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.
I noticed that columns were also disallowed in INSTEAD OF triggers,
but haven't dug further into those just yet.
What say?
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 cb0bb4b39efff33d7eee7cd4cde7879b7107d250 Mon Sep 17 00:00:00 2001
From: David Fetter <da...@fetter.org>
Date: Fri, 27 Dec 2019 18:57:31 -0800
Subject: [PATCH v1] 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..ee2b63cb03 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1030,10 +1030,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');
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index c21b6c124e..840d6617da 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -727,10 +727,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');
--------------2.24.1--
