[PATCH] Allow queries in WHEN expression of FOR EACH STATEMENT triggers

From 56d010c925db41ffe689044ba215640600976748 Mon Sep 17 00:00:00 2001
From: Joe Wildish <joe@sql.dev>
Date: Wed, 30 Dec 2020 19:20:10 +0000
Subject: [PATCH] Allow queries in WHEN expression of FOR EACH STATEMENT
 triggers

Adds support to the trigger system to allow queries in the WHEN condition
of FOR EACH STATEMENT triggers. The expression can contain references to
the transition tables NEW and OLD, as well as the table which the
trigger is attached to, but other table references are disallowed.
---
 doc/src/sgml/ref/create_trigger.sgml   |  45 +-
 doc/src/sgml/trigger.sgml              |   7 +-
 src/backend/commands/trigger.c         | 597 ++++++++++++++++++++-----
 src/backend/parser/parse_expr.c        |   4 +-
 src/backend/utils/adt/ruleutils.c      |  94 ++--
 src/include/nodes/execnodes.h          |   2 +-
 src/test/regress/expected/triggers.out |  73 ++-
 src/test/regress/sql/triggers.sql      |  63 +++
 8 files changed, 699 insertions(+), 186 deletions(-)

diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 561af989a4..47f9a65fe4 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -160,13 +160,14 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
   </informaltable>
 
   <para>
-   Also, a trigger definition can specify a Boolean <literal>WHEN</literal>
-   condition, which will be tested to see whether the trigger should
+   A trigger definition can specify a Boolean <literal>WHEN</literal>
+   condition which will be tested to see whether the trigger should
    be fired.  In row-level triggers the <literal>WHEN</literal> condition can
-   examine the old and/or new values of columns of the row.  Statement-level
-   triggers can also have <literal>WHEN</literal> conditions, although the feature
-   is not so useful for them since the condition cannot refer to any values
-   in the table.
+   examine the old and/or new values of the columns of each row  which the
+   statement affects.  Statement-level triggers can also have
+   <literal>WHEN</literal> conditions, and are able to examine old and/or new
+   transition relations, that comprise of all rows either deleted or inserted
+   respectively by the triggering statement.
   </para>
 
   <para>
@@ -375,23 +376,41 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
       will actually be executed.  If <literal>WHEN</literal> is specified, the
       function will only be called if the <replaceable
       class="parameter">condition</replaceable> returns <literal>true</literal>.
-      In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</literal>
+     </para>
+
+     <para>
+      In <literal>FOR EACH ROW</literal> triggers the <literal>WHEN</literal>
       condition can refer to columns of the old and/or new row values
       by writing <literal>OLD.<replaceable
       class="parameter">column_name</replaceable></literal> or
       <literal>NEW.<replaceable
       class="parameter">column_name</replaceable></literal> respectively.
-      Of course, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
-      and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>.
+      The <literal>WHEN</literal> expression of a <literal>FOR EACH ROW</literal>
+      trigger cannot contain a subquery.
      </para>
 
-     <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
-      conditions.
+     <para>
+      In <literal>FOR EACH STATEMENT</literal> triggers the
+      <literal>WHEN</literal> condition can refer to the transition relations
+      <literal>OLD</literal> and <literal>NEW</literal>, and the relation that the
+      trigger is for. No other relations can be referenced. As <literal>OLD</literal>
+      and <literal>NEW</literal> are relations rather than row values, a
+      <replaceable class="parameter">condition</replaceable> will typically comprise of
+      subquery expressions defined over those relations. Refer to
+      <xref linkend="functions-subquery"/> for subquery expression examples.
      </para>
 
      <para>
-      Currently, <literal>WHEN</literal> expressions cannot contain
-      subqueries.
+       In both <literal>FOR EACH ROW</literal> and <literal>FOR EACH STATEMENT</literal>
+       triggers, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
+       row values or transition tables, and <literal>DELETE</literal> triggers cannot refer
+       to <literal>NEW</literal> row values or transition tables. However,
+       <literal>UPDATE</literal> triggers are able to refer to both <literal>OLD</literal>
+       and <literal>NEW</literal>
+     </para>
+
+     <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
+      conditions.
      </para>
 
      <para>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 4a0e74652f..6161b0c0e5 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -277,10 +277,9 @@
     A trigger definition can also specify a Boolean <literal>WHEN</literal>
     condition, which will be tested to see whether the trigger should
     be fired.  In row-level triggers the <literal>WHEN</literal> condition can
-    examine the old and/or new values of columns of the row.  (Statement-level
-    triggers can also have <literal>WHEN</literal> conditions, although the feature
-    is not so useful for them.)  In a <literal>BEFORE</literal> trigger, the
-    <literal>WHEN</literal>
+    examine the old and/or new values of columns of the row, whereas statement-level
+    triggers can examine old and/or new relations that contain the relevant rows.
+    In a <literal>BEFORE</literal> trigger, the <literal>WHEN</literal>
     condition is evaluated just before the function is or would be executed,
     so using <literal>WHEN</literal> is not materially different from testing the
     same condition at the beginning of the trigger function.  However, in
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index c336b238aa..38c7db7560 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -36,12 +36,15 @@
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/execPartition.h"
+#include "executor/tstoreReceiver.h"
 #include "miscadmin.h"
 #include "nodes/bitmapset.h"
+#include "nodes/nodeFuncs.h"
 #include "nodes/makefuncs.h"
 #include "optimizer/optimizer.h"
 #include "parser/parse_clause.h"
 #include "parser/parse_collate.h"
+#include "parser/parse_expr.h"
 #include "parser/parse_func.h"
 #include "parser/parse_relation.h"
 #include "parser/parsetree.h"
@@ -59,6 +62,7 @@
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
+#include "utils/ruleutils.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
 #include "utils/tuplestore.h"
@@ -70,6 +74,17 @@ int			SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
 /* How many levels deep into trigger execution are we? */
 static int	MyTriggerDepth = 0;
 
+/* Context for walking the WHEN expression when validating it */
+typedef struct CheckWhenExprWalkerContext
+{
+	ParseState *pstate;
+	Relation	rel;
+	int16		tgtype;
+	char	   *oldtablename;
+	char	   *newtablename;
+	List	   *rtable;
+}			CheckWhenExprWalkerContext;
+
 /*
  * Note that similar macros also exist in executor/execMain.c.  There does not
  * appear to be any good header to put them into, given the structures that
@@ -92,7 +107,8 @@ static bool GetTupleForTrigger(EState *estate,
 static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 						   Trigger *trigger, TriggerEvent event,
 						   Bitmapset *modifiedCols,
-						   TupleTableSlot *oldslot, TupleTableSlot *newslot);
+						   TupleTableSlot *oldslot, TupleTableSlot *newslot,
+						   Tuplestorestate *oldstore, Tuplestorestate *newstore);
 static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata,
 									 int tgindx,
 									 FmgrInfo *finfo,
@@ -105,6 +121,13 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 								  TransitionCaptureState *transition_capture);
 static void AfterTriggerEnlargeQueryState(void);
 static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void check_when_expr(Node *clause, ParseState *pstate,
+							Relation rel, int16 tgtype,
+							char *oldtablename, char *newtablename);
+static bool check_when_expr_walker(Node *node, CheckWhenExprWalkerContext * ctx);
+static void make_and_register_ENR(QueryEnvironment *env, char *name,
+								  Oid relid, Tuplestorestate *data);
+static void unregister_and_free_ENR(QueryEnvironment *env, char *name);
 
 
 /*
@@ -552,29 +575,53 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
 	if (!whenClause && stmt->whenClause)
 	{
 		ParseState *pstate;
-		ParseNamespaceItem *nsitem;
-		List	   *varList;
-		ListCell   *lc;
+		Oid			relid;
 
 		/* Set up a pstate to parse with */
 		pstate = make_parsestate(NULL);
 		pstate->p_sourcetext = queryString;
+		relid = rel->rd_rel->oid;
 
-		/*
-		 * Set up nsitems for OLD and NEW references.
-		 *
-		 * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
-		 */
-		nsitem = addRangeTableEntryForRelation(pstate, rel,
-											   AccessShareLock,
-											   makeAlias("old", NIL),
-											   false, false);
-		addNSItemToQuery(pstate, nsitem, false, true, true);
-		nsitem = addRangeTableEntryForRelation(pstate, rel,
-											   AccessShareLock,
-											   makeAlias("new", NIL),
-											   false, false);
-		addNSItemToQuery(pstate, nsitem, false, true, true);
+		if (TRIGGER_FOR_ROW(tgtype))
+		{
+			/*
+			 * Set up nsitems for OLD/NEW references in a FOR EACH ROW
+			 * trigger.
+			 *
+			 * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+			 */
+			ParseNamespaceItem *nsitem;
+
+			nsitem = addRangeTableEntryForRelation(pstate, rel,
+												   AccessShareLock,
+												   makeAlias("old", NIL),
+												   false, false);
+			addNSItemToQuery(pstate, nsitem, false, true, true);
+			nsitem = addRangeTableEntryForRelation(pstate, rel,
+												   AccessShareLock,
+												   makeAlias("new", NIL),
+												   false, false);
+			addNSItemToQuery(pstate, nsitem, false, true, true);
+
+		}
+		else
+		{
+			/*
+			 * Set up QueryEnvironment for OLD/NEW tables in a FOR EACH
+			 * STATEMENT trigger.
+			 *
+			 * This allows subqueries to resolve column names etc.
+			 */
+			pstate->p_queryEnv = create_queryEnv();
+
+			if (newtablename != NULL)
+				make_and_register_ENR(pstate->p_queryEnv, newtablename,
+									  relid, NULL);
+
+			if (oldtablename != NULL)
+				make_and_register_ENR(pstate->p_queryEnv, oldtablename,
+									  relid, NULL);
+		}
 
 		/* Transform expression.  Copy to be sure we don't modify original */
 		whenClause = transformWhereClause(pstate,
@@ -585,79 +632,33 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
 		assign_expr_collations(pstate, whenClause);
 
 		/*
-		 * Check for disallowed references to OLD/NEW.
+		 * Check for disallowed references to OLD/NEW or other relations.
 		 *
-		 * NB: pull_var_clause is okay here only because we don't allow
-		 * subselects in WHEN clauses; it would fail to examine the contents
-		 * of subselects.
+		 * If we are a FOR EACH ROW trigger then the use of OLD/NEW is as a
+		 * row variable. If we are a FOR EACH STATEMENT trigger then OLD/NEW
+		 * are tables. Any expressions in WHEN should be written accordingly.
 		 */
-		varList = pull_var_clause(whenClause, 0);
-		foreach(lc, varList)
-		{
-			Var		   *var = (Var *) lfirst(lc);
+		check_when_expr(whenClause, pstate, rel, tgtype,
+						oldtablename, newtablename);
 
-			switch (var->varno)
-			{
-				case PRS2_OLD_VARNO:
-					if (!TRIGGER_FOR_ROW(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("statement trigger's WHEN condition cannot reference column values"),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_INSERT(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
-								 parser_errposition(pstate, var->location)));
-					/* system columns are okay here */
-					break;
-				case PRS2_NEW_VARNO:
-					if (!TRIGGER_FOR_ROW(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("statement trigger's WHEN condition cannot reference column values"),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_DELETE(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
-								 parser_errposition(pstate, var->location)));
-					if (var->varattno < 0 && TRIGGER_FOR_BEFORE(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-								 errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_BEFORE(tgtype) &&
-						var->varattno == 0 &&
-						RelationGetDescr(rel)->constr &&
-						RelationGetDescr(rel)->constr->has_generated_stored)
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
-								 errdetail("A whole-row reference is used and the table contains generated columns."),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_BEFORE(tgtype) &&
-						var->varattno > 0 &&
-						TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated)
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
-								 errdetail("Column \"%s\" is a generated column.",
-										   NameStr(TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attname)),
-								 parser_errposition(pstate, var->location)));
-					break;
-				default:
-					/* can't happen without add_missing_from, so just elog */
-					elog(ERROR, "trigger WHEN condition cannot contain references to other relations");
-					break;
-			}
-		}
-
-		/* we'll need the rtable for recordDependencyOnExpr */
+		/*
+		 * We'll need the rtable for recordDependencyOnExpr for FOR EACH ROW
+		 * triggers.
+		 */
 		whenRtable = pstate->p_rtable;
 
 		qual = nodeToString(whenClause);
 
+		/* Free any ephemeral relations */
+		if (!TRIGGER_FOR_ROW(tgtype))
+		{
+			if (newtablename != NULL)
+				unregister_and_free_ENR(pstate->p_queryEnv, newtablename);
+
+			if (oldtablename != NULL)
+				unregister_and_free_ENR(pstate->p_queryEnv, oldtablename);
+		}
+
 		free_parsestate(pstate);
 	}
 	else if (!whenClause)
@@ -1117,8 +1118,32 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
 	 * expression (eg, functions, as well as any columns used).
 	 */
 	if (whenRtable != NIL)
-		recordDependencyOnExpr(&myself, whenClause, whenRtable,
-							   DEPENDENCY_NORMAL);
+	{
+		if (TRIGGER_FOR_ROW(tgtype))
+			recordDependencyOnExpr(&myself, whenClause, whenRtable,
+								   DEPENDENCY_NORMAL);
+		else
+		{
+			/*
+			 * recordDependencyOnExpr expects to get a Query rather than a
+			 * SubLink. If we were to just pass it the Boolean expression then
+			 * it won't recurse.
+			 */
+			Query	   *whenQuery;
+
+			whenQuery = makeNode(Query);
+			whenQuery->commandType = CMD_SELECT;
+			whenQuery->querySource = QSRC_ORIGINAL;
+			whenQuery->hasSubLinks = true;
+			whenQuery->jointree = makeFromExpr(NIL, NULL);
+			whenQuery->targetList = list_make1(makeTargetEntry((Expr *) whenClause, 1, "WHEN", false));
+
+			recordDependencyOnExpr(&myself, (Node *) whenQuery, NIL,
+								   DEPENDENCY_NORMAL);
+
+			pfree(whenQuery);
+		}
+	}
 
 	/* Post creation hook for new trigger */
 	InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
@@ -2214,7 +2239,7 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_INSERT))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, NULL))
+							NULL, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -2268,7 +2293,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_INSERT))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, slot))
+							NULL, NULL, slot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2324,10 +2349,11 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
 					 TransitionCaptureState *transition_capture)
 {
 	TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+	int			event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
 
 	if ((trigdesc && trigdesc->trig_insert_after_row) ||
 		(transition_capture && transition_capture->tcs_insert_new_table))
-		AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT,
+		AfterTriggerSaveEvent(estate, relinfo, event,
 							  true, NULL, slot,
 							  recheckIndexes, NULL,
 							  transition_capture);
@@ -2359,7 +2385,7 @@ ExecIRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_INSERT))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, slot))
+							NULL, NULL, slot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2428,7 +2454,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_DELETE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, NULL))
+							NULL, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -2523,7 +2549,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
 								  TRIGGER_TYPE_DELETE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, slot, NULL))
+							NULL, slot, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigslot = slot;
@@ -2556,6 +2582,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
 {
 	TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
 	TupleTableSlot *slot = ExecGetTriggerOldSlot(estate, relinfo);
+	int			event = TRIGGER_EVENT_DELETE | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
 
 	if ((trigdesc && trigdesc->trig_delete_after_row) ||
 		(transition_capture && transition_capture->tcs_delete_old_table))
@@ -2572,7 +2599,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
 		else
 			ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
 
-		AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE,
+		AfterTriggerSaveEvent(estate, relinfo, event,
 							  true, slot, NULL, NIL, NULL,
 							  transition_capture);
 	}
@@ -2606,7 +2633,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_DELETE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, slot, NULL))
+							NULL, slot, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigslot = slot;
@@ -2663,7 +2690,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_UPDATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							updatedCols, NULL, NULL))
+							updatedCols, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -2772,7 +2799,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
 								  TRIGGER_TYPE_UPDATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							updatedCols, oldslot, newslot))
+							updatedCols, oldslot, newslot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2843,6 +2870,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 {
 	TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
 	TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
+	int			event = TRIGGER_EVENT_UPDATE | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
 
 	ExecClearTuple(oldslot);
 
@@ -2868,7 +2896,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 		else if (fdw_trigtuple != NULL)
 			ExecForceStoreHeapTuple(fdw_trigtuple, oldslot, false);
 
-		AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE,
+		AfterTriggerSaveEvent(estate, relinfo, event,
 							  true, oldslot, newslot, recheckIndexes,
 							  GetAllUpdatedColumns(relinfo, estate),
 							  transition_capture);
@@ -2905,7 +2933,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_UPDATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, oldslot, newslot))
+							NULL, oldslot, newslot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2971,7 +2999,7 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_TRUNCATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, NULL))
+							NULL, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -3122,7 +3150,8 @@ static bool
 TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 			   Trigger *trigger, TriggerEvent event,
 			   Bitmapset *modifiedCols,
-			   TupleTableSlot *oldslot, TupleTableSlot *newslot)
+			   TupleTableSlot *oldslot, TupleTableSlot *newslot,
+			   Tuplestorestate *oldstore, Tuplestorestate *newstore)
 {
 	/* Check replication-role-dependent enable state */
 	if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
@@ -3161,8 +3190,10 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 			return false;
 	}
 
-	/* Check for WHEN clause */
-	if (trigger->tgqual)
+	/*
+	 * Check for WHEN clause in a FOR EACH ROW trigger.
+	 */
+	if (TRIGGER_FIRED_FOR_ROW(event) && trigger->tgqual)
 	{
 		ExprState **predicate;
 		ExprContext *econtext;
@@ -3214,6 +3245,109 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 			return false;
 	}
 
+	/*
+	 * Check for WHEN clause in a FOR EACH STATEMENT trigger.
+	 */
+	if (TRIGGER_FIRED_FOR_STATEMENT(event) && trigger->tgqual)
+	{
+		Node	   *qual;
+		char	   *qualstr;
+		Query	   *query;
+		PlannedStmt *stmt;
+		DestReceiver *dest;
+		Tuplestorestate *store;
+		QueryEnvironment *env;
+		QueryDesc  *querydesc;
+		TupleDesc	tupdesc;
+		TupleTableSlot *whenslot;
+		Oid			relid;
+		bool		when;
+		bool		isnull;
+
+		Assert((!TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable) || oldstore) &&
+			   (!TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable) || newstore));
+
+		qual = stringToNode(trigger->tgqual);
+
+		/*
+		 * Create a QueryEnvironment containing the OLD/NEW transition tables.
+		 */
+		env = create_queryEnv();
+		relid = relinfo->ri_RelationDesc->rd_id;
+
+		if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable))
+			make_and_register_ENR(env, trigger->tgoldtable, relid, oldstore);
+
+		if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable))
+			make_and_register_ENR(env, trigger->tgnewtable, relid, newstore);
+
+		/*
+		 * Create a statement to plan and execute to evaluate the WHEN
+		 * expression. This will be of the form `SELECT <when-expression>`.
+		 */
+		query = makeNode(Query);
+		query->commandType = CMD_SELECT;
+		query->querySource = QSRC_ORIGINAL;
+		query->hasSubLinks = true;
+		query->jointree = makeFromExpr(NIL, NULL);
+		query->targetList = list_make1(makeTargetEntry((Expr *) qual, 1, "WHEN", false));
+
+		/*
+		 * Plan the statement. No need to rewrite as it can only refer to the
+		 * transition tables OLD and NEW, and the relation which is being
+		 * triggered upon.
+		 */
+		stmt = pg_plan_query(query, trigger->tgqual, 0, NULL);
+		dest = CreateDestReceiver(DestTuplestore);
+		store = tuplestore_begin_heap(false, false, work_mem);
+		tupdesc = CreateTemplateTupleDesc(1);
+		whenslot = MakeSingleTupleTableSlot(tupdesc, &TTSOpsMinimalTuple);
+
+		/*
+		 * Deparse the qualifier expression so that the executor can emit a
+		 * sensible query string if we are being logged.
+		 */
+		qualstr = deparse_expression(qual, NIL, false, false);
+		SetTuplestoreDestReceiverParams(dest, store,
+										CurrentMemoryContext, false,
+										NULL, NULL);
+		querydesc = CreateQueryDesc(stmt, qualstr, GetActiveSnapshot(),
+									InvalidSnapshot, dest, NULL, env, 0);
+
+		/*
+		 * Execute the statement.
+		 */
+		ExecutorStart(querydesc, 0);
+		ExecutorRun(querydesc, ForwardScanDirection, 0L, true);
+		ExecutorFinish(querydesc);
+		ExecutorEnd(querydesc);
+
+		/*
+		 * Get the Boolean result. The expression could be NULL in which case we
+		 * treat the result as false.
+		 */
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "WHEN", BOOLOID, -1, 0);
+		tuplestore_gettupleslot(store, true, false, whenslot);
+		when = DatumGetBool(slot_getattr(whenslot, 1, &isnull));
+		tuplestore_end(store);
+
+		/*
+		 * Cleanup.
+		 */
+		ExecClearTuple(whenslot);
+		ExecDropSingleTupleTableSlot(whenslot);
+		FreeQueryDesc(querydesc);
+
+		if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable))
+			unregister_and_free_ENR(env, trigger->tgoldtable);
+
+		if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable))
+			unregister_and_free_ENR(env, trigger->tgnewtable);
+
+		if (isnull || !when)
+			return false;
+	}
+
 	return true;
 }
 
@@ -5473,14 +5607,14 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 		 * NULL when the event is for a row being inserted, whereas NEW is
 		 * NULL when the event is for a row being deleted.
 		 */
-		Assert(!(event == TRIGGER_EVENT_DELETE && delete_old_table &&
+		Assert(!(TRIGGER_FIRED_BY_DELETE(event) && delete_old_table &&
 				 TupIsNull(oldslot)));
-		Assert(!(event == TRIGGER_EVENT_INSERT && insert_new_table &&
+		Assert(!(TRIGGER_FIRED_BY_INSERT(event) && insert_new_table &&
 				 TupIsNull(newslot)));
 
 		if (!TupIsNull(oldslot) &&
-			((event == TRIGGER_EVENT_DELETE && delete_old_table) ||
-			 (event == TRIGGER_EVENT_UPDATE && update_old_table)))
+			((TRIGGER_FIRED_BY_DELETE(event) && delete_old_table) ||
+			 (TRIGGER_FIRED_BY_UPDATE(event) && update_old_table)))
 		{
 			Tuplestorestate *old_tuplestore;
 
@@ -5506,8 +5640,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				tuplestore_puttupleslot(old_tuplestore, oldslot);
 		}
 		if (!TupIsNull(newslot) &&
-			((event == TRIGGER_EVENT_INSERT && insert_new_table) ||
-			 (event == TRIGGER_EVENT_UPDATE && update_new_table)))
+			((TRIGGER_FIRED_BY_INSERT(event) && insert_new_table) ||
+			 (TRIGGER_FIRED_BY_UPDATE(event) && update_new_table)))
 		{
 			Tuplestorestate *new_tuplestore;
 
@@ -5545,10 +5679,10 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 		 * of them to be NULL.
 		 */
 		if (trigdesc == NULL ||
-			(event == TRIGGER_EVENT_DELETE && !trigdesc->trig_delete_after_row) ||
-			(event == TRIGGER_EVENT_INSERT && !trigdesc->trig_insert_after_row) ||
-			(event == TRIGGER_EVENT_UPDATE && !trigdesc->trig_update_after_row) ||
-			(event == TRIGGER_EVENT_UPDATE && (TupIsNull(oldslot) ^ TupIsNull(newslot))))
+			(TRIGGER_FIRED_BY_DELETE(event) && !trigdesc->trig_delete_after_row) ||
+			(TRIGGER_FIRED_BY_INSERT(event) && !trigdesc->trig_insert_after_row) ||
+			(TRIGGER_FIRED_BY_UPDATE(event) && !trigdesc->trig_update_after_row) ||
+			(TRIGGER_FIRED_BY_UPDATE(event) && (TupIsNull(oldslot) ^ TupIsNull(newslot))))
 			return;
 	}
 
@@ -5564,7 +5698,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 	 * if so.  This preserves the behavior that statement-level triggers fire
 	 * just once per statement and fire after row-level triggers.
 	 */
-	switch (event)
+	switch (event & TRIGGER_EVENT_OPMASK)
 	{
 		case TRIGGER_EVENT_INSERT:
 			tgtype_event = TRIGGER_TYPE_INSERT;
@@ -5581,8 +5715,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				Assert(newslot == NULL);
 				ItemPointerSetInvalid(&(new_event.ate_ctid1));
 				ItemPointerSetInvalid(&(new_event.ate_ctid2));
-				cancel_prior_stmt_triggers(RelationGetRelid(rel),
-										   CMD_INSERT, event);
+				cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_INSERT,
+										   (event & TRIGGER_EVENT_OPMASK));
 			}
 			break;
 		case TRIGGER_EVENT_DELETE:
@@ -5600,8 +5734,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				Assert(newslot == NULL);
 				ItemPointerSetInvalid(&(new_event.ate_ctid1));
 				ItemPointerSetInvalid(&(new_event.ate_ctid2));
-				cancel_prior_stmt_triggers(RelationGetRelid(rel),
-										   CMD_DELETE, event);
+				cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_DELETE,
+										   (event & TRIGGER_EVENT_OPMASK));
 			}
 			break;
 		case TRIGGER_EVENT_UPDATE:
@@ -5619,8 +5753,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				Assert(newslot == NULL);
 				ItemPointerSetInvalid(&(new_event.ate_ctid1));
 				ItemPointerSetInvalid(&(new_event.ate_ctid2));
-				cancel_prior_stmt_triggers(RelationGetRelid(rel),
-										   CMD_UPDATE, event);
+				cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_UPDATE,
+										   (event & TRIGGER_EVENT_OPMASK));
 			}
 			break;
 		case TRIGGER_EVENT_TRUNCATE:
@@ -5631,13 +5765,14 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 			ItemPointerSetInvalid(&(new_event.ate_ctid2));
 			break;
 		default:
-			elog(ERROR, "invalid after-trigger event code: %d", event);
+			elog(ERROR, "invalid after-trigger event code: %d",
+				 (event & TRIGGER_EVENT_OPMASK));
 			tgtype_event = 0;	/* keep compiler quiet */
 			break;
 	}
 
 	if (!(relkind == RELKIND_FOREIGN_TABLE && row_trigger))
-		new_event.ate_flags = (row_trigger && event == TRIGGER_EVENT_UPDATE) ?
+		new_event.ate_flags = (row_trigger && TRIGGER_FIRED_BY_UPDATE(event)) ?
 			AFTER_TRIGGER_2CTID : AFTER_TRIGGER_1CTID;
 	/* else, we'll initialize ate_flags for each trigger */
 
@@ -5646,14 +5781,25 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 	for (i = 0; i < trigdesc->numtriggers; i++)
 	{
 		Trigger    *trigger = &trigdesc->triggers[i];
+		Tuplestorestate *oldstore,
+				   *newstore;
+
+		if (transition_capture)
+		{
+			Assert(transition_capture->tcs_private);
+			oldstore = transition_capture->tcs_private->old_tuplestore;
+			newstore = transition_capture->tcs_private->new_tuplestore;
+		}
+		else
+			oldstore = newstore = NULL;
 
 		if (!TRIGGER_TYPE_MATCHES(trigger->tgtype,
 								  tgtype_level,
 								  TRIGGER_TYPE_AFTER,
 								  tgtype_event))
 			continue;
-		if (!TriggerEnabled(estate, relinfo, trigger, event,
-							modifiedCols, oldslot, newslot))
+		if (!TriggerEnabled(estate, relinfo, trigger, event, modifiedCols,
+							oldslot, newslot, oldstore, newstore))
 			continue;
 
 		if (relkind == RELKIND_FOREIGN_TABLE && row_trigger)
@@ -5783,6 +5929,209 @@ before_stmt_triggers_fired(Oid relid, CmdType cmdType)
 	return result;
 }
 
+
+/*
+ * Detect inappropriate WHEN expressions.  Only certain triggers can make
+ * reference to either the NEW or OLD transition tables/row (e.g. a DELETE
+ * trigger cannot make reference to NEW).  FOR EACH STATEMENT triggers must
+ * treat NEW/OLD as tables whereas FOR EACH ROW triggers treat them as row
+ * values.
+ */
+static void
+check_when_expr(Node *clause, ParseState *pstate,
+				Relation rel, int16 tgtype,
+				char *oldtablename, char *newtablename)
+{
+	CheckWhenExprWalkerContext context;
+
+	context.pstate = pstate;
+	context.rel = rel;
+	context.tgtype = tgtype;
+	context.oldtablename = oldtablename;
+	context.newtablename = newtablename;
+	context.rtable = NIL;
+
+	(void) check_when_expr_walker(clause, (void *) &context);
+}
+
+
+static bool
+check_when_expr_walker(Node *node, CheckWhenExprWalkerContext * ctx)
+{
+	if (node == NULL)
+		return false;
+
+	if (IsA(node, Query) && TRIGGER_FOR_ROW(ctx->tgtype))
+	{
+		/*
+		 * FOR EACH ROW triggers can only use NEW/OLD as row values so there
+		 * is no support for subqueries.
+		 */
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+				 errmsg("row trigger's WHEN condition cannot use subqueries")));
+		return false;
+	}
+	else if (IsA(node, Query) && !TRIGGER_FOR_ROW(ctx->tgtype))
+	{
+		/*
+		 * FOR EACH STATEMENT triggers can have subqueries, so recurse.
+		 */
+		Query	   *query;
+		List	   *rtable;
+		bool		result;
+
+		query = castNode(Query, node);
+		rtable = ctx->rtable;
+
+		ctx->rtable = query->rtable;
+		result = query_tree_walker(query, check_when_expr_walker, ctx, 0);
+		ctx->rtable = rtable;
+
+		return result;
+	}
+	else if (IsA(node, Var))
+	{
+		Var		   *var;
+		bool		old,
+					new;
+
+		var = castNode(Var, node);
+
+		/*
+		 * Determine if this Var is for NEW, OLD, or neither.
+		 */
+		if (TRIGGER_FOR_ROW(ctx->tgtype))
+		{
+			old = (var->varno == PRS2_OLD_VARNO);
+			new = (var->varno == PRS2_NEW_VARNO);
+		}
+		else
+		{
+			RangeTblEntry *entry;
+
+			entry = rt_fetch(var->varno, ctx->rtable);
+			old = entry->relkind == RTE_NAMEDTUPLESTORE &&
+				ctx->oldtablename != NULL &&
+				strcmp(entry->enrname, ctx->oldtablename) != 0;
+			new = entry->relkind == RTE_NAMEDTUPLESTORE &&
+				ctx->newtablename != NULL &&
+				strcmp(entry->enrname, ctx->newtablename) != 0;
+		}
+		Assert(!(new && old));	/* sanity */
+
+		/*
+		 * Now, make checks on the basis of if the Var is OLD or NEW.
+		 */
+		if (old)
+		{
+			if (TRIGGER_FOR_INSERT(ctx->tgtype))
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+						 errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
+						 parser_errposition(ctx->pstate, var->location)));
+			/* system columns are okay here */
+		}
+		if (new)
+		{
+			if (TRIGGER_FOR_DELETE(ctx->tgtype))
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+						 errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
+						 parser_errposition(ctx->pstate, var->location)));
+			if (var->varattno < 0 && TRIGGER_FOR_BEFORE(ctx->tgtype))
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
+						 parser_errposition(ctx->pstate, var->location)));
+			if (TRIGGER_FOR_BEFORE(ctx->tgtype) &&
+				var->varattno == 0 &&
+				RelationGetDescr(ctx->rel)->constr &&
+				RelationGetDescr(ctx->rel)->constr->has_generated_stored)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+						 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+						 errdetail("A whole-row reference is used and the table contains generated columns."),
+						 parser_errposition(ctx->pstate, var->location)));
+			if (TRIGGER_FOR_BEFORE(ctx->tgtype) &&
+				var->varattno > 0 &&
+				TupleDescAttr(RelationGetDescr(ctx->rel), var->varattno - 1)->attgenerated)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+						 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+						 errdetail("Column \"%s\" is a generated column.",
+								   NameStr(TupleDescAttr(RelationGetDescr(ctx->rel), var->varattno - 1)->attname)),
+						 parser_errposition(ctx->pstate, var->location)));
+		}
+		return false;
+	}
+	else if (IsA(node, RangeTblRef))
+	{
+		RangeTblRef *ref;
+		RangeTblEntry *entry;
+
+		ref = castNode(RangeTblRef, node);
+		entry = rt_fetch(ref->rtindex, ctx->rtable);
+
+		switch (entry->rtekind)
+		{
+			case RTE_SUBQUERY:
+			case RTE_JOIN:
+			case RTE_VALUES:
+			case RTE_CTE:
+			case RTE_RESULT:
+			case RTE_NAMEDTUPLESTORE:
+				/* All OK */
+				break;
+			case RTE_RELATION:
+				{
+					if (entry->relid != RelationGetRelid(ctx->rel))
+						ereport(ERROR,
+								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+								 errmsg("trigger's WHEN condition cannot contain references to other relations")));
+					break;
+				}
+			case RTE_FUNCTION:
+			case RTE_TABLEFUNC:
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+						 errmsg("trigger's WHEN condition cannot contain table functions")));
+				break;
+		}
+		return false;
+	}
+	return expression_tree_walker(node, check_when_expr_walker, ctx);
+}
+
+static void
+make_and_register_ENR(QueryEnvironment *env, char *name,
+					  Oid relid, Tuplestorestate *data)
+{
+	EphemeralNamedRelation enr;
+
+	enr = palloc(sizeof(EphemeralNamedRelationData));
+	MemSet(enr, 0, sizeof(EphemeralNamedRelationData));
+	enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+	enr->md.reliddesc = relid;
+	enr->md.name = name;
+	enr->reldata = (void *) data;
+
+	register_ENR(env, enr);
+}
+
+static void
+unregister_and_free_ENR(QueryEnvironment *env, char *name)
+{
+	EphemeralNamedRelation enr;
+
+	enr = get_ENR(env, name);
+	if (enr)
+	{
+		unregister_ENR(env, name);
+		pfree(enr);
+	}
+}
+
 /*
  * If we previously queued a set of AFTER STATEMENT triggers for the given
  * relation + operation, and they've not been fired yet, cancel them.  The
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index ffc96e2a6f..26db88ff2e 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1723,6 +1723,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
 		case EXPR_KIND_RETURNING:
 		case EXPR_KIND_VALUES:
 		case EXPR_KIND_VALUES_SINGLE:
+		case EXPR_KIND_TRIGGER_WHEN:
 			/* okay */
 			break;
 		case EXPR_KIND_CHECK_CONSTRAINT:
@@ -1745,9 +1746,6 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
 		case EXPR_KIND_EXECUTE_PARAMETER:
 			err = _("cannot use subquery in EXECUTE parameter");
 			break;
-		case EXPR_KIND_TRIGGER_WHEN:
-			err = _("cannot use subquery in trigger WHEN condition");
-			break;
 		case EXPR_KIND_PARTITION_BOUND:
 			err = _("cannot use subquery in partition bound");
 			break;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 7d4443e807..c3ca54217b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -996,53 +996,15 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 	if (!isnull)
 	{
 		Node	   *qual;
-		char		relkind;
 		deparse_context context;
-		deparse_namespace dpns;
-		RangeTblEntry *oldrte;
-		RangeTblEntry *newrte;
 
 		appendStringInfoString(&buf, "WHEN (");
 
 		qual = stringToNode(TextDatumGetCString(value));
 
-		relkind = get_rel_relkind(trigrec->tgrelid);
-
-		/* Build minimal OLD and NEW RTEs for the rel */
-		oldrte = makeNode(RangeTblEntry);
-		oldrte->rtekind = RTE_RELATION;
-		oldrte->relid = trigrec->tgrelid;
-		oldrte->relkind = relkind;
-		oldrte->rellockmode = AccessShareLock;
-		oldrte->alias = makeAlias("old", NIL);
-		oldrte->eref = oldrte->alias;
-		oldrte->lateral = false;
-		oldrte->inh = false;
-		oldrte->inFromCl = true;
-
-		newrte = makeNode(RangeTblEntry);
-		newrte->rtekind = RTE_RELATION;
-		newrte->relid = trigrec->tgrelid;
-		newrte->relkind = relkind;
-		newrte->rellockmode = AccessShareLock;
-		newrte->alias = makeAlias("new", NIL);
-		newrte->eref = newrte->alias;
-		newrte->lateral = false;
-		newrte->inh = false;
-		newrte->inFromCl = true;
-
-		/* Build two-element rtable */
-		memset(&dpns, 0, sizeof(dpns));
-		dpns.rtable = list_make2(oldrte, newrte);
-		dpns.subplans = NIL;
-		dpns.ctes = NIL;
-		dpns.appendrels = NULL;
-		set_rtable_names(&dpns, NIL, NULL);
-		set_simple_column_names(&dpns);
-
-		/* Set up context with one-deep namespace stack */
+		/* Set up context */
 		context.buf = &buf;
-		context.namespaces = list_make1(&dpns);
+		context.namespaces = NIL;
 		context.windowClause = NIL;
 		context.windowTList = NIL;
 		context.varprefix = true;
@@ -1052,6 +1014,54 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 		context.special_exprkind = EXPR_KIND_NONE;
 		context.appendparents = NULL;
 
+		/* For ROW triggers we need to build the OLD and NEW RTEs for the rel.
+		 * This isn't necessary for STATEMENT triggers as their WHEN expression
+		 * will already have a range table defined in the EXISTS expressions. */
+		if (TRIGGER_FOR_ROW(trigrec->tgtype))
+		{
+			char				relkind;
+			deparse_namespace	dpns;
+			RangeTblEntry		*oldrte;
+			RangeTblEntry		*newrte;
+
+			relkind = get_rel_relkind(trigrec->tgrelid);
+
+			/* Build minimal OLD and NEW RTEs for the rel */
+			oldrte = makeNode(RangeTblEntry);
+			oldrte->rtekind = RTE_RELATION;
+			oldrte->relid = trigrec->tgrelid;
+			oldrte->relkind = relkind;
+			oldrte->rellockmode = AccessShareLock;
+			oldrte->alias = makeAlias("old", NIL);
+			oldrte->eref = oldrte->alias;
+			oldrte->lateral = false;
+			oldrte->inh = false;
+			oldrte->inFromCl = true;
+
+			newrte = makeNode(RangeTblEntry);
+			newrte->rtekind = RTE_RELATION;
+			newrte->relid = trigrec->tgrelid;
+			newrte->relkind = relkind;
+			newrte->rellockmode = AccessShareLock;
+			newrte->alias = makeAlias("new", NIL);
+			newrte->eref = newrte->alias;
+			newrte->lateral = false;
+			newrte->inh = false;
+			newrte->inFromCl = true;
+
+			/* Build two-element rtable */
+			memset(&dpns, 0, sizeof(dpns));
+			dpns.rtable = list_make2(oldrte, newrte);
+			dpns.subplans = NIL;
+			dpns.ctes = NIL;
+			dpns.appendrels = NULL;
+			set_rtable_names(&dpns, NIL, NULL);
+			set_simple_column_names(&dpns);
+
+			/* Make the deparse context aware */
+			context.namespaces = list_make1(&dpns);
+		}
+
 		get_rule_expr(qual, &context, false);
 
 		appendStringInfoString(&buf, ") ");
@@ -10434,6 +10444,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
 			case RTE_CTE:
 				appendStringInfoString(buf, quote_identifier(rte->ctename));
 				break;
+			case RTE_NAMEDTUPLESTORE:
+				/* Ephemeral RTE */
+				appendStringInfo(buf, "%s", rte->enrname);
+				break;
 			default:
 				elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind);
 				break;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 61ba4c3666..8194c2a1ca 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -426,7 +426,7 @@ typedef struct ResultRelInfo
 	/* cached lookup info for trigger functions */
 	FmgrInfo   *ri_TrigFunctions;
 
-	/* array of trigger WHEN expr states */
+	/* array of trigger WHEN expr states for FOR EACH ROW triggers */
 	ExprState **ri_TrigWhenExprs;
 
 	/* optional runtime measurements for triggers */
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1dc525251a..19fd0de68e 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -588,6 +588,77 @@ create trigger oid_unchanged_trig after update on table_with_oids
 update table_with_oids set a = a + 1;
 NOTICE:  trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW
 drop table table_with_oids;
+-- Test FOR EACH STATEMENT triggers with queries in their WHEN condition
+--
+-- disable other triggers
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_stmt_trig;
+SELECT a, b FROM main_table ORDER BY a, b;
+ a  | b  
+----+----
+  6 | 10
+ 21 | 20
+ 30 | 40
+ 31 | 10
+ 50 | 35
+ 50 | 60
+ 81 | 15
+    |   
+(8 rows)
+
+-- legal WHEN expressions
+CREATE TRIGGER after_insert AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (500 <= ANY(SELECT b FROM NEW))
+  EXECUTE PROCEDURE trigger_func('after_insert');
+INSERT INTO main_table (a, b) VALUES -- after_insert won't fire
+  (101, 498),
+  (102, 499);
+INSERT INTO main_table (a, b) VALUES -- after_insert will fire
+  (103, 501),
+  (104, -99);
+NOTICE:  trigger_func(after_insert) called: action = INSERT, when = AFTER, level = STATEMENT
+DROP TRIGGER after_insert ON main_table;
+CREATE TRIGGER after_delete AFTER DELETE ON main_table
+  REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT
+  WHEN (0 >= ANY(SELECT b FROM OLD))
+  EXECUTE PROCEDURE trigger_func('after_delete');
+DELETE FROM main_table WHERE a IN (101, 102); -- after delete won't fire
+DELETE FROM main_table WHERE a IN (103, 104); -- after delete will fire
+NOTICE:  trigger_func(after_delete) called: action = DELETE, when = AFTER, level = STATEMENT
+DROP TRIGGER after_delete ON main_table;
+CREATE TRIGGER after_update AFTER UPDATE ON main_table
+  REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (EXISTS (SELECT 1 FROM new JOIN old ON (new.a = old.a) WHERE new.a < 50 AND new.b < old.b))
+  EXECUTE PROCEDURE trigger_func('after_update');
+UPDATE main_table SET b = (b + 1); -- after_update won't fire
+UPDATE main_table SET b = (b - 1); -- after_update will fire
+NOTICE:  trigger_func(after_update) called: action = UPDATE, when = AFTER, level = STATEMENT
+DROP TRIGGER after_update ON main_table;
+-- illegal WHEN expressions
+CREATE TABLE other_table (n INTEGER NOT NULL PRIMARY KEY);
+CREATE FUNCTION some_function() RETURNS TABLE (n INTEGER) LANGUAGE SQL AS $$ SELECT n FROM other_table $$;
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (1 = ANY (SELECT a FROM NEW INTERSECT SELECT n FROM other_table))
+  EXECUTE PROCEDURE trigger_func('error_due_to_other_table');
+ERROR:  trigger's WHEN condition cannot contain references to other relations
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (EXISTS (SELECT 1 FROM NEW JOIN some_function() AS sf ON (new.a = sf.n)))
+  EXECUTE PROCEDURE trigger_func('error_due_to_some_function');
+ERROR:  trigger's WHEN condition cannot contain table functions
+DROP FUNCTION some_function();
+DROP TABLE other_table;
+-- re-enable other triggers
+ALTER TABLE main_table ENABLE TRIGGER after_upd_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+--
+-- Done testing FOR EACH STATEMENT with queries in the WHEN condition
 -- Test column-level triggers
 DROP TRIGGER after_upd_row_trig ON main_table;
 CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
@@ -700,7 +771,7 @@ LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0)
 CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
 FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
 EXECUTE PROCEDURE trigger_func('error_stmt_when');
-ERROR:  statement trigger's WHEN condition cannot reference column values
+ERROR:  missing FROM-clause entry for table "old"
 LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
                                  ^
 -- check dependency restrictions
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index bebe276ef4..b9257f252a 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -369,6 +369,69 @@ create trigger oid_unchanged_trig after update on table_with_oids
 update table_with_oids set a = a + 1;
 drop table table_with_oids;
 
+-- Test FOR EACH STATEMENT triggers with queries in their WHEN condition
+--
+
+-- disable other triggers
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_stmt_trig;
+
+SELECT a, b FROM main_table ORDER BY a, b;
+
+-- legal WHEN expressions
+CREATE TRIGGER after_insert AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (500 <= ANY(SELECT b FROM NEW))
+  EXECUTE PROCEDURE trigger_func('after_insert');
+INSERT INTO main_table (a, b) VALUES -- after_insert won't fire
+  (101, 498),
+  (102, 499);
+INSERT INTO main_table (a, b) VALUES -- after_insert will fire
+  (103, 501),
+  (104, -99);
+DROP TRIGGER after_insert ON main_table;
+
+CREATE TRIGGER after_delete AFTER DELETE ON main_table
+  REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT
+  WHEN (0 >= ANY(SELECT b FROM OLD))
+  EXECUTE PROCEDURE trigger_func('after_delete');
+DELETE FROM main_table WHERE a IN (101, 102); -- after delete won't fire
+DELETE FROM main_table WHERE a IN (103, 104); -- after delete will fire
+DROP TRIGGER after_delete ON main_table;
+
+CREATE TRIGGER after_update AFTER UPDATE ON main_table
+  REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (EXISTS (SELECT 1 FROM new JOIN old ON (new.a = old.a) WHERE new.a < 50 AND new.b < old.b))
+  EXECUTE PROCEDURE trigger_func('after_update');
+UPDATE main_table SET b = (b + 1); -- after_update won't fire
+UPDATE main_table SET b = (b - 1); -- after_update will fire
+DROP TRIGGER after_update ON main_table;
+
+-- illegal WHEN expressions
+CREATE TABLE other_table (n INTEGER NOT NULL PRIMARY KEY);
+CREATE FUNCTION some_function() RETURNS TABLE (n INTEGER) LANGUAGE SQL AS $$ SELECT n FROM other_table $$;
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (1 = ANY (SELECT a FROM NEW INTERSECT SELECT n FROM other_table))
+  EXECUTE PROCEDURE trigger_func('error_due_to_other_table');
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (EXISTS (SELECT 1 FROM NEW JOIN some_function() AS sf ON (new.a = sf.n)))
+  EXECUTE PROCEDURE trigger_func('error_due_to_some_function');
+DROP FUNCTION some_function();
+DROP TABLE other_table;
+
+-- re-enable other triggers
+ALTER TABLE main_table ENABLE TRIGGER after_upd_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+
+--
+-- Done testing FOR EACH STATEMENT with queries in the WHEN condition
+
 -- Test column-level triggers
 DROP TRIGGER after_upd_row_trig ON main_table;
 
-- 
2.28.0

From 32cc660e51dc8a157e98cf3f1862fc149b4f68ea Mon Sep 17 00:00:00 2001
From: Joe Wildish <joe@sql.dev>
Date: Wed, 2 Jun 2021 12:48:34 +0100
Subject: [PATCH] Allow queries in WHEN expression of FOR EACH STATEMENT
 triggers

Adds support to the trigger system to allow queries in the WHEN condition
of FOR EACH STATEMENT triggers. The expression can contain references to
the transition tables NEW and OLD, as well as the table which the
trigger is attached to, but other table references are disallowed.
---
 doc/src/sgml/ref/create_trigger.sgml   |  45 +-
 doc/src/sgml/trigger.sgml              |   7 +-
 src/backend/commands/tablecmds.c       |   2 +
 src/backend/commands/trigger.c         | 852 ++++++++++++++++++++-----
 src/backend/parser/parse_expr.c        |   4 +-
 src/backend/utils/adt/ruleutils.c      | 100 +--
 src/include/nodes/execnodes.h          |   2 +-
 src/include/utils/reltrigger.h         |   1 +
 src/test/regress/expected/triggers.out |  66 +-
 src/test/regress/sql/triggers.sql      |  57 ++
 10 files changed, 898 insertions(+), 238 deletions(-)

diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 561af989a4..47f9a65fe4 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -160,13 +160,14 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
   </informaltable>
 
   <para>
-   Also, a trigger definition can specify a Boolean <literal>WHEN</literal>
-   condition, which will be tested to see whether the trigger should
+   A trigger definition can specify a Boolean <literal>WHEN</literal>
+   condition which will be tested to see whether the trigger should
    be fired.  In row-level triggers the <literal>WHEN</literal> condition can
-   examine the old and/or new values of columns of the row.  Statement-level
-   triggers can also have <literal>WHEN</literal> conditions, although the feature
-   is not so useful for them since the condition cannot refer to any values
-   in the table.
+   examine the old and/or new values of the columns of each row  which the
+   statement affects.  Statement-level triggers can also have
+   <literal>WHEN</literal> conditions, and are able to examine old and/or new
+   transition relations, that comprise of all rows either deleted or inserted
+   respectively by the triggering statement.
   </para>
 
   <para>
@@ -375,23 +376,41 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
       will actually be executed.  If <literal>WHEN</literal> is specified, the
       function will only be called if the <replaceable
       class="parameter">condition</replaceable> returns <literal>true</literal>.
-      In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</literal>
+     </para>
+
+     <para>
+      In <literal>FOR EACH ROW</literal> triggers the <literal>WHEN</literal>
       condition can refer to columns of the old and/or new row values
       by writing <literal>OLD.<replaceable
       class="parameter">column_name</replaceable></literal> or
       <literal>NEW.<replaceable
       class="parameter">column_name</replaceable></literal> respectively.
-      Of course, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
-      and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>.
+      The <literal>WHEN</literal> expression of a <literal>FOR EACH ROW</literal>
+      trigger cannot contain a subquery.
      </para>
 
-     <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
-      conditions.
+     <para>
+      In <literal>FOR EACH STATEMENT</literal> triggers the
+      <literal>WHEN</literal> condition can refer to the transition relations
+      <literal>OLD</literal> and <literal>NEW</literal>, and the relation that the
+      trigger is for. No other relations can be referenced. As <literal>OLD</literal>
+      and <literal>NEW</literal> are relations rather than row values, a
+      <replaceable class="parameter">condition</replaceable> will typically comprise of
+      subquery expressions defined over those relations. Refer to
+      <xref linkend="functions-subquery"/> for subquery expression examples.
      </para>
 
      <para>
-      Currently, <literal>WHEN</literal> expressions cannot contain
-      subqueries.
+       In both <literal>FOR EACH ROW</literal> and <literal>FOR EACH STATEMENT</literal>
+       triggers, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
+       row values or transition tables, and <literal>DELETE</literal> triggers cannot refer
+       to <literal>NEW</literal> row values or transition tables. However,
+       <literal>UPDATE</literal> triggers are able to refer to both <literal>OLD</literal>
+       and <literal>NEW</literal>
+     </para>
+
+     <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
+      conditions.
      </para>
 
      <para>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 4a0e74652f..6161b0c0e5 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -277,10 +277,9 @@
     A trigger definition can also specify a Boolean <literal>WHEN</literal>
     condition, which will be tested to see whether the trigger should
     be fired.  In row-level triggers the <literal>WHEN</literal> condition can
-    examine the old and/or new values of columns of the row.  (Statement-level
-    triggers can also have <literal>WHEN</literal> conditions, although the feature
-    is not so useful for them.)  In a <literal>BEFORE</literal> trigger, the
-    <literal>WHEN</literal>
+    examine the old and/or new values of columns of the row, whereas statement-level
+    triggers can examine old and/or new relations that contain the relevant rows.
+    In a <literal>BEFORE</literal> trigger, the <literal>WHEN</literal>
     condition is evaluated just before the function is or would be executed,
     so using <literal>WHEN</literal> is not materially different from testing the
     same condition at the beginning of the trigger function.  However, in
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 028e8ac46b..45821c8590 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11005,6 +11005,7 @@ validateForeignKeyConstraint(char *conname,
 	 */
 	MemSet(&trig, 0, sizeof(trig));
 	trig.tgoid = InvalidOid;
+	trig.tgrelid = InvalidOid;
 	trig.tgname = conname;
 	trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
 	trig.tgisinternal = true;
@@ -18559,6 +18560,7 @@ ATDetachCheckNoForeignKeyRefs(Relation partition)
 
 		MemSet(&trig, 0, sizeof(trig));
 		trig.tgoid = InvalidOid;
+		trig.tgrelid = InvalidOid;
 		trig.tgname = NameStr(constrForm->conname);
 		trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
 		trig.tgisinternal = true;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 07c73f39de..7290f8c51c 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -36,10 +36,11 @@
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "executor/execPartition.h"
+#include "executor/spi.h"
 #include "miscadmin.h"
 #include "nodes/bitmapset.h"
+#include "nodes/nodeFuncs.h"
 #include "nodes/makefuncs.h"
-#include "optimizer/optimizer.h"
 #include "parser/parse_clause.h"
 #include "parser/parse_collate.h"
 #include "parser/parse_func.h"
@@ -59,10 +60,12 @@
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
+#include "utils/ruleutils.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
 #include "utils/tuplestore.h"
 
+#define WHENQUERYCACHE_INIT_SIZE		64
 
 /* GUC variables */
 int			SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
@@ -70,6 +73,25 @@ int			SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
 /* How many levels deep into trigger execution are we? */
 static int	MyTriggerDepth = 0;
 
+/* Context for validating a trigger's WHEN expression */
+typedef struct ValidateWhenExprContext
+{
+	ParseState *pstate;       /* expression parse state */
+	Relation	rel;          /* relation being triggered against */
+	int16		tgtype;       /* trigger type data */
+	char	   *oldtablename; /* declared name for OLD */
+	char	   *newtablename; /* declared name for NEW */
+	List		*rtable;      /* current range table, if any */
+} ValidateWhenExprContext;
+
+typedef struct WhenQueryCacheEntry {
+	Oid			tgoid;	/* trigger oid */
+	SPIPlanPtr	plan;	/* prepared plan for WHEN expression */
+} WhenQueryCacheEntry;
+
+/* Local data */
+static HTAB *WhenQueryCache = NULL;
+
 /* Local function prototypes */
 static void SetTriggerFlags(TriggerDesc *trigdesc, Trigger *trigger);
 static bool GetTupleForTrigger(EState *estate,
@@ -82,7 +104,15 @@ static bool GetTupleForTrigger(EState *estate,
 static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 						   Trigger *trigger, TriggerEvent event,
 						   Bitmapset *modifiedCols,
-						   TupleTableSlot *oldslot, TupleTableSlot *newslot);
+						   TupleTableSlot *oldslot, TupleTableSlot *newslot,
+						   Tuplestorestate *oldstore, Tuplestorestate *newstore);
+static bool EvalARWhenExpr(EState *estate, ResultRelInfo *relinfo,
+						   Trigger *trigger,
+						   TupleTableSlot *oldslot,
+						   TupleTableSlot *newslot);
+static bool EvalASWhenExpr(Trigger *trigger,
+						   Tuplestorestate *oldstore,
+						   Tuplestorestate *newstore);
 static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata,
 									 int tgindx,
 									 FmgrInfo *finfo,
@@ -95,6 +125,21 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 								  TransitionCaptureState *transition_capture);
 static void AfterTriggerEnlargeQueryState(void);
 static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void WhenQueryCache_Init(void);
+static SPIPlanPtr WhenQueryCache_FetchPlan(Oid key);
+static void WhenQueryCache_StorePlan(Oid key, SPIPlanPtr plan);
+static ParseState *makeWhenExprParseState(const char *queryString, int16 tgtype,
+										  Relation rel, char *oldtablename,
+										  char *newtablename);
+static void freeWhenExprParseState(ParseState *pstate,
+								   char *oldtablename, char *newtablename);
+static void validateWhenExpr(ParseState *pstate, Node *whenClause,
+							 Relation rel, int16 tgtype,
+							 char *oldtablename, char *newtablename);
+static bool validateWhenExpr_Walker(Node *node,
+									ValidateWhenExprContext *ctx);
+static EphemeralNamedRelation makeENR(char *name, Oid relid,
+									  Tuplestorestate *data);
 
 
 /*
@@ -178,6 +223,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
 	bool		trigger_exists = false;
 	Oid			existing_constraint_oid = InvalidOid;
 	bool		existing_isInternal = false;
+	bool		recordDeps = false;
 
 	if (OidIsValid(relOid))
 		rel = table_open(relOid, ShareRowExclusiveLock);
@@ -541,125 +587,57 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
 	 */
 	if (!whenClause && stmt->whenClause)
 	{
-		ParseState *pstate;
-		ParseNamespaceItem *nsitem;
-		List	   *varList;
-		ListCell   *lc;
-
-		/* Set up a pstate to parse with */
-		pstate = make_parsestate(NULL);
-		pstate->p_sourcetext = queryString;
+		ParseState				*pstate;
 
 		/*
-		 * Set up nsitems for OLD and NEW references.
-		 *
-		 * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+		 * Setup the parse state.  We will re-use the rtable from the parse
+		 * state afterwards to help record dependencies.
 		 */
-		nsitem = addRangeTableEntryForRelation(pstate, rel,
-											   AccessShareLock,
-											   makeAlias("old", NIL),
-											   false, false);
-		addNSItemToQuery(pstate, nsitem, false, true, true);
-		nsitem = addRangeTableEntryForRelation(pstate, rel,
-											   AccessShareLock,
-											   makeAlias("new", NIL),
-											   false, false);
-		addNSItemToQuery(pstate, nsitem, false, true, true);
+		pstate = makeWhenExprParseState(queryString, tgtype, rel,
+										oldtablename, newtablename);
+
+		/* Copy to be sure we don't modify original when validating. */
+		whenClause = copyObject(stmt->whenClause);
 
-		/* Transform expression.  Copy to be sure we don't modify original */
+		/* Transform expression. */
 		whenClause = transformWhereClause(pstate,
-										  copyObject(stmt->whenClause),
+										  whenClause,
 										  EXPR_KIND_TRIGGER_WHEN,
 										  "WHEN");
-		/* we have to fix its collations too */
+
+		/* We have to fix its collations too. */
 		assign_expr_collations(pstate, whenClause);
 
 		/*
-		 * Check for disallowed references to OLD/NEW.
+		 * Check for disallowed references to OLD, NEW, or other relations.
 		 *
-		 * NB: pull_var_clause is okay here only because we don't allow
-		 * subselects in WHEN clauses; it would fail to examine the contents
-		 * of subselects.
+		 * If we are a FOR EACH ROW trigger then the use of OLD/NEW is as a
+		 * row variable. If we are a FOR EACH STATEMENT trigger then OLD/NEW
+		 * are tables. Any expressions in WHEN should be written accordingly.
 		 */
-		varList = pull_var_clause(whenClause, 0);
-		foreach(lc, varList)
-		{
-			Var		   *var = (Var *) lfirst(lc);
-
-			switch (var->varno)
-			{
-				case PRS2_OLD_VARNO:
-					if (!TRIGGER_FOR_ROW(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("statement trigger's WHEN condition cannot reference column values"),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_INSERT(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
-								 parser_errposition(pstate, var->location)));
-					/* system columns are okay here */
-					break;
-				case PRS2_NEW_VARNO:
-					if (!TRIGGER_FOR_ROW(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("statement trigger's WHEN condition cannot reference column values"),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_DELETE(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
-								 parser_errposition(pstate, var->location)));
-					if (var->varattno < 0 && TRIGGER_FOR_BEFORE(tgtype))
-						ereport(ERROR,
-								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-								 errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_BEFORE(tgtype) &&
-						var->varattno == 0 &&
-						RelationGetDescr(rel)->constr &&
-						RelationGetDescr(rel)->constr->has_generated_stored)
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
-								 errdetail("A whole-row reference is used and the table contains generated columns."),
-								 parser_errposition(pstate, var->location)));
-					if (TRIGGER_FOR_BEFORE(tgtype) &&
-						var->varattno > 0 &&
-						TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated)
-						ereport(ERROR,
-								(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
-								 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
-								 errdetail("Column \"%s\" is a generated column.",
-										   NameStr(TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attname)),
-								 parser_errposition(pstate, var->location)));
-					break;
-				default:
-					/* can't happen without add_missing_from, so just elog */
-					elog(ERROR, "trigger WHEN condition cannot contain references to other relations");
-					break;
-			}
-		}
+		validateWhenExpr(pstate, whenClause, rel, tgtype,
+						 oldtablename, newtablename);
 
-		/* we'll need the rtable for recordDependencyOnExpr */
+		/* Keep the rtable for recordDependencyOnExpr for ROW triggers. */
 		whenRtable = pstate->p_rtable;
-
 		qual = nodeToString(whenClause);
+		recordDeps = true;
 
-		free_parsestate(pstate);
+		/* Free parse state stuff */
+		freeWhenExprParseState(pstate, oldtablename, newtablename);
 	}
 	else if (!whenClause)
 	{
 		whenClause = NULL;
 		whenRtable = NIL;
 		qual = NULL;
+		recordDeps = false;
 	}
 	else
 	{
 		qual = nodeToString(whenClause);
 		whenRtable = NIL;
+		recordDeps = false;
 	}
 
 	/*
@@ -1106,9 +1084,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
 	 * If it has a WHEN clause, add dependencies on objects mentioned in the
 	 * expression (eg, functions, as well as any columns used).
 	 */
-	if (whenRtable != NIL)
+	if (recordDeps)
+	{
+		Assert((TRIGGER_FOR_ROW(tgtype) && whenRtable != NULL) ||
+			   (!TRIGGER_FOR_ROW(tgtype) && whenRtable == NULL));
 		recordDependencyOnExpr(&myself, whenClause, whenRtable,
 							   DEPENDENCY_NORMAL);
+	}
 
 	/* Post creation hook for new trigger */
 	InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
@@ -1684,6 +1666,7 @@ RelationBuildTriggers(Relation relation)
 		build = &(triggers[numtrigs]);
 
 		build->tgoid = pg_trigger->oid;
+		build->tgrelid = pg_trigger->tgrelid;
 		build->tgname = DatumGetCString(DirectFunctionCall1(nameout,
 															NameGetDatum(&pg_trigger->tgname)));
 		build->tgfoid = pg_trigger->tgfoid;
@@ -2204,7 +2187,7 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_INSERT))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, NULL))
+							NULL, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -2258,7 +2241,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_INSERT))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, slot))
+							NULL, NULL, slot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2314,10 +2297,11 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
 					 TransitionCaptureState *transition_capture)
 {
 	TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+	int			event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
 
 	if ((trigdesc && trigdesc->trig_insert_after_row) ||
 		(transition_capture && transition_capture->tcs_insert_new_table))
-		AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT,
+		AfterTriggerSaveEvent(estate, relinfo, event,
 							  true, NULL, slot,
 							  recheckIndexes, NULL,
 							  transition_capture);
@@ -2349,7 +2333,7 @@ ExecIRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_INSERT))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, slot))
+							NULL, NULL, slot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2418,7 +2402,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_DELETE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, NULL))
+							NULL, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -2513,7 +2497,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
 								  TRIGGER_TYPE_DELETE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, slot, NULL))
+							NULL, slot, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigslot = slot;
@@ -2545,6 +2529,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
 					 TransitionCaptureState *transition_capture)
 {
 	TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+	int			event = TRIGGER_EVENT_DELETE | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
 
 	if ((trigdesc && trigdesc->trig_delete_after_row) ||
 		(transition_capture && transition_capture->tcs_delete_old_table))
@@ -2563,7 +2548,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
 		else
 			ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
 
-		AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE,
+		AfterTriggerSaveEvent(estate, relinfo, event,
 							  true, slot, NULL, NIL, NULL,
 							  transition_capture);
 	}
@@ -2597,7 +2582,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_DELETE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, slot, NULL))
+							NULL, slot, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigslot = slot;
@@ -2657,7 +2642,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_UPDATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							updatedCols, NULL, NULL))
+							updatedCols, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -2771,7 +2756,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
 								  TRIGGER_TYPE_UPDATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							updatedCols, oldslot, newslot))
+							updatedCols, oldslot, newslot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2831,6 +2816,10 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 					 TransitionCaptureState *transition_capture)
 {
 	TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+	TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
+	int			event = TRIGGER_EVENT_UPDATE | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
+
+	ExecClearTuple(oldslot);
 
 	if ((trigdesc && trigdesc->trig_update_after_row) ||
 		(transition_capture &&
@@ -2858,7 +2847,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 		else
 			ExecClearTuple(oldslot);
 
-		AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE,
+		AfterTriggerSaveEvent(estate, relinfo, event,
 							  true, oldslot, newslot, recheckIndexes,
 							  ExecGetAllUpdatedCols(relinfo, estate),
 							  transition_capture);
@@ -2895,7 +2884,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
 								  TRIGGER_TYPE_UPDATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, oldslot, newslot))
+							NULL, oldslot, newslot, NULL, NULL))
 			continue;
 
 		if (!newtuple)
@@ -2961,7 +2950,7 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
 								  TRIGGER_TYPE_TRUNCATE))
 			continue;
 		if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
-							NULL, NULL, NULL))
+							NULL, NULL, NULL, NULL, NULL))
 			continue;
 
 		LocTriggerData.tg_trigger = trigger;
@@ -3112,7 +3101,8 @@ static bool
 TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 			   Trigger *trigger, TriggerEvent event,
 			   Bitmapset *modifiedCols,
-			   TupleTableSlot *oldslot, TupleTableSlot *newslot)
+			   TupleTableSlot *oldslot, TupleTableSlot *newslot,
+			   Tuplestorestate *oldstore, Tuplestorestate *newstore)
 {
 	/* Check replication-role-dependent enable state */
 	if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
@@ -3151,60 +3141,194 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
 			return false;
 	}
 
-	/* Check for WHEN clause */
-	if (trigger->tgqual)
+	/*
+	 * Evaluate the WHEN clause for a FOR EACH ROW trigger, if any.
+	 */
+	if (TRIGGER_FIRED_FOR_ROW(event) && trigger->tgqual)
+		return EvalARWhenExpr(estate, relinfo, trigger,
+							  oldslot, newslot);
+
+	/*
+	 * Evaluate the WHEN clause for a FOR EACH STATEMENT trigger, if any.
+	 */
+	if (TRIGGER_FIRED_FOR_STATEMENT(event) && trigger->tgqual)
+		return EvalASWhenExpr(trigger, oldstore, newstore);
+
+	return true;
+}
+
+
+static bool
+EvalARWhenExpr(EState *estate, ResultRelInfo *relinfo,
+			   Trigger *trigger,
+			   TupleTableSlot *oldslot,
+			   TupleTableSlot *newslot)
+{
+	ExprState		**predicate;
+	ExprContext		*econtext;
+	MemoryContext	oldContext;
+	int				i;
+
+	Assert(estate != NULL);
+
+	/*
+	 * trigger is an element of relinfo->ri_TrigDesc->triggers[]; find the
+	 * matching element of relinfo->ri_TrigWhenExprs[]
+	 */
+	i = trigger - relinfo->ri_TrigDesc->triggers;
+	predicate = &relinfo->ri_TrigWhenExprs[i];
+
+	/*
+	 * If first time through for this WHEN expression, build expression
+	 * nodetrees for it.  Keep them in the per-query memory context so
+	 * they'll survive throughout the query.
+	 */
+	if (*predicate == NULL)
 	{
-		ExprState **predicate;
-		ExprContext *econtext;
-		MemoryContext oldContext;
-		int			i;
+		Node	   *tgqual;
+
+		oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
+		tgqual = stringToNode(trigger->tgqual);
+		/* Change references to OLD and NEW to INNER_VAR and OUTER_VAR */
+		ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER_VAR, 0);
+		ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER_VAR, 0);
+		/* ExecPrepareQual wants implicit-AND form */
+		tgqual = (Node *) make_ands_implicit((Expr *) tgqual);
+		*predicate = ExecPrepareQual((List *) tgqual, estate);
+		MemoryContextSwitchTo(oldContext);
+	}
 
-		Assert(estate != NULL);
+	/*
+	 * We will use the EState's per-tuple context for evaluating WHEN
+	 * expressions (creating it if it's not already there).
+	 */
+	econtext = GetPerTupleExprContext(estate);
 
-		/*
-		 * trigger is an element of relinfo->ri_TrigDesc->triggers[]; find the
-		 * matching element of relinfo->ri_TrigWhenExprs[]
-		 */
-		i = trigger - relinfo->ri_TrigDesc->triggers;
-		predicate = &relinfo->ri_TrigWhenExprs[i];
+	/*
+	 * Finally evaluate the expression, making the old and/or new tuples
+	 * available as INNER_VAR/OUTER_VAR respectively.
+	 */
+	econtext->ecxt_innertuple = oldslot;
+	econtext->ecxt_outertuple = newslot;
+	if (!ExecQual(*predicate, econtext))
+		return false;
 
-		/*
-		 * If first time through for this WHEN expression, build expression
-		 * nodetrees for it.  Keep them in the per-query memory context so
-		 * they'll survive throughout the query.
-		 */
-		if (*predicate == NULL)
-		{
-			Node	   *tgqual;
-
-			oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
-			tgqual = stringToNode(trigger->tgqual);
-			/* Change references to OLD and NEW to INNER_VAR and OUTER_VAR */
-			ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER_VAR, 0);
-			ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER_VAR, 0);
-			/* ExecPrepareQual wants implicit-AND form */
-			tgqual = (Node *) make_ands_implicit((Expr *) tgqual);
-			*predicate = ExecPrepareQual((List *) tgqual, estate);
-			MemoryContextSwitchTo(oldContext);
-		}
+	return true;
+}
 
-		/*
-		 * We will use the EState's per-tuple context for evaluating WHEN
-		 * expressions (creating it if it's not already there).
-		 */
-		econtext = GetPerTupleExprContext(estate);
+
+
+static bool
+EvalASWhenExpr(Trigger *trigger, Tuplestorestate *oldstore, Tuplestorestate *newstore)
+{
+	EphemeralNamedRelation	oldenr = NULL,
+							newenr = NULL;
+	SPIPlanPtr				spi_plan;
+	bool					result;
+
+	if (SPI_connect() != SPI_OK_CONNECT)
+		elog(ERROR, "SPI_connect failed");
+
+	/*
+	 * Attach relevant ENR tables into the query environment.
+	 *
+	 * This needs doing now in case we need to create a plan for the
+	 * expression.
+	 */
+	if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable))
+	{
+		oldenr = makeENR(trigger->tgoldtable, trigger->tgrelid, oldstore);
+		if (SPI_register_relation(oldenr) != SPI_OK_REL_REGISTER)
+			elog(ERROR, "SPI_register_relation returned %s for OLD",
+				 SPI_result_code_string(SPI_result));
+	}
+	if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable))
+	{
+		newenr = makeENR(trigger->tgnewtable, trigger->tgrelid, newstore);
+		if (SPI_register_relation(newenr) != SPI_OK_REL_REGISTER)
+			elog(ERROR, "SPI_register_relation returned %s for NEW",
+				 SPI_result_code_string(SPI_result));
+	}
+
+	/* Check for a cached WHEN query expression plan. */
+	if ((spi_plan = WhenQueryCache_FetchPlan(trigger->tgoid)) == NULL)
+	{
+		StringInfoData	buffer;
+		Node 			*node;
+		char			*expr;
+
+		/* Convert the qual into an expression suitable for SPI */
+		node = stringToNode(trigger->tgqual);
+		expr = deparse_expression(node, NIL, false, false);
+
+		/* Create the query text to evaluate the expression */
+		initStringInfo(&buffer);
+		appendStringInfoString(&buffer, "SELECT ");
+		appendStringInfoString(&buffer, expr);
+		appendStringInfoString(&buffer, " AS \"WHEN\"");
+
+		/* Prepare the plan and save it into the cache */
+		spi_plan = SPI_prepare(buffer.data, 0, NULL);
+
+		if (spi_plan == NULL)
+			elog(ERROR, "SPI_prepare returned %s for %s",
+				 SPI_result_code_string(SPI_result), buffer.data);
+
+		SPI_keepplan(spi_plan);
+		WhenQueryCache_StorePlan(trigger->tgoid, spi_plan);
+	}
+
+	/*
+	 * Execute the plan and get the result.
+	 */
+	if (SPI_execute_plan(spi_plan, NULL, NULL, true, 1) > 0)
+	{
+		TupleDesc 	desc;
+		HeapTuple 	tuple;
+		Datum		datum;
+		bool		null;
+
+		/* The query should always return one-and-only-one tuple. */
+		Assert(SPI_processed == 1);
+
+		/* Get the first (and only) tuple, first (and only) attribute. */
+		tuple = SPI_tuptable->vals[0];
+		desc = SPI_tuptable->tupdesc;
+		datum = SPI_getbinval(tuple, desc, 1, &null);
 
 		/*
-		 * Finally evaluate the expression, making the old and/or new tuples
-		 * available as INNER_VAR/OUTER_VAR respectively.
+		 * It is possible that the expression evaluated to NULL.  We
+		 * take that to mean "not true" rather than e.g. "unknown" hence
+		 * mapping it to "false".
 		 */
-		econtext->ecxt_innertuple = oldslot;
-		econtext->ecxt_outertuple = newslot;
-		if (!ExecQual(*predicate, econtext))
-			return false;
+		result = null ? false : DatumGetBool(datum);
 	}
+	else
+		elog(ERROR, "SPI_execute_plan returned %s",
+			 SPI_result_code_string(SPI_result));
 
-	return true;
+	/*
+	 * Detach ENR tables before finishing with SPI.
+	 */
+	if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable))
+	{
+		if (SPI_unregister_relation(trigger->tgoldtable) != SPI_OK_REL_UNREGISTER)
+			elog(ERROR, "SPI_unregister_relation returned %s for OLD",
+				 SPI_result_code_string(SPI_result));
+		pfree(oldenr);
+	}
+	if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable))
+	{
+		if (SPI_unregister_relation(trigger->tgnewtable) != SPI_OK_REL_UNREGISTER)
+			elog(ERROR, "SPI_unregister_relation returned %s for NEW",
+				 SPI_result_code_string(SPI_result));
+		pfree(newenr);
+	}
+
+	if (SPI_finish() != SPI_OK_FINISH)
+		elog(ERROR, "SPI_finish failed");
+
+	return result;
 }
 
 
@@ -5494,14 +5618,14 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 		 * NULL when the event is for a row being inserted, whereas NEW is
 		 * NULL when the event is for a row being deleted.
 		 */
-		Assert(!(event == TRIGGER_EVENT_DELETE && delete_old_table &&
+		Assert(!(TRIGGER_FIRED_BY_DELETE(event) && delete_old_table &&
 				 TupIsNull(oldslot)));
-		Assert(!(event == TRIGGER_EVENT_INSERT && insert_new_table &&
+		Assert(!(TRIGGER_FIRED_BY_INSERT(event) && insert_new_table &&
 				 TupIsNull(newslot)));
 
 		if (!TupIsNull(oldslot) &&
-			((event == TRIGGER_EVENT_DELETE && delete_old_table) ||
-			 (event == TRIGGER_EVENT_UPDATE && update_old_table)))
+			((TRIGGER_FIRED_BY_DELETE(event) && delete_old_table) ||
+			 (TRIGGER_FIRED_BY_UPDATE(event) && update_old_table)))
 		{
 			Tuplestorestate *old_tuplestore;
 
@@ -5520,8 +5644,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				tuplestore_puttupleslot(old_tuplestore, oldslot);
 		}
 		if (!TupIsNull(newslot) &&
-			((event == TRIGGER_EVENT_INSERT && insert_new_table) ||
-			 (event == TRIGGER_EVENT_UPDATE && update_new_table)))
+			((TRIGGER_FIRED_BY_INSERT(event) && insert_new_table) ||
+			 (TRIGGER_FIRED_BY_UPDATE(event) && update_new_table)))
 		{
 			Tuplestorestate *new_tuplestore;
 
@@ -5551,10 +5675,10 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 		 * of them to be NULL.
 		 */
 		if (trigdesc == NULL ||
-			(event == TRIGGER_EVENT_DELETE && !trigdesc->trig_delete_after_row) ||
-			(event == TRIGGER_EVENT_INSERT && !trigdesc->trig_insert_after_row) ||
-			(event == TRIGGER_EVENT_UPDATE && !trigdesc->trig_update_after_row) ||
-			(event == TRIGGER_EVENT_UPDATE && (TupIsNull(oldslot) ^ TupIsNull(newslot))))
+			(TRIGGER_FIRED_BY_DELETE(event) && !trigdesc->trig_delete_after_row) ||
+			(TRIGGER_FIRED_BY_INSERT(event) && !trigdesc->trig_insert_after_row) ||
+			(TRIGGER_FIRED_BY_UPDATE(event) && !trigdesc->trig_update_after_row) ||
+			(TRIGGER_FIRED_BY_UPDATE(event) && (TupIsNull(oldslot) ^ TupIsNull(newslot))))
 			return;
 	}
 
@@ -5570,7 +5694,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 	 * if so.  This preserves the behavior that statement-level triggers fire
 	 * just once per statement and fire after row-level triggers.
 	 */
-	switch (event)
+	switch (event & TRIGGER_EVENT_OPMASK)
 	{
 		case TRIGGER_EVENT_INSERT:
 			tgtype_event = TRIGGER_TYPE_INSERT;
@@ -5587,8 +5711,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				Assert(newslot == NULL);
 				ItemPointerSetInvalid(&(new_event.ate_ctid1));
 				ItemPointerSetInvalid(&(new_event.ate_ctid2));
-				cancel_prior_stmt_triggers(RelationGetRelid(rel),
-										   CMD_INSERT, event);
+				cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_INSERT,
+										   (event & TRIGGER_EVENT_OPMASK));
 			}
 			break;
 		case TRIGGER_EVENT_DELETE:
@@ -5606,8 +5730,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				Assert(newslot == NULL);
 				ItemPointerSetInvalid(&(new_event.ate_ctid1));
 				ItemPointerSetInvalid(&(new_event.ate_ctid2));
-				cancel_prior_stmt_triggers(RelationGetRelid(rel),
-										   CMD_DELETE, event);
+				cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_DELETE,
+										   (event & TRIGGER_EVENT_OPMASK));
 			}
 			break;
 		case TRIGGER_EVENT_UPDATE:
@@ -5625,8 +5749,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 				Assert(newslot == NULL);
 				ItemPointerSetInvalid(&(new_event.ate_ctid1));
 				ItemPointerSetInvalid(&(new_event.ate_ctid2));
-				cancel_prior_stmt_triggers(RelationGetRelid(rel),
-										   CMD_UPDATE, event);
+				cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_UPDATE,
+										   (event & TRIGGER_EVENT_OPMASK));
 			}
 			break;
 		case TRIGGER_EVENT_TRUNCATE:
@@ -5637,13 +5761,14 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 			ItemPointerSetInvalid(&(new_event.ate_ctid2));
 			break;
 		default:
-			elog(ERROR, "invalid after-trigger event code: %d", event);
+			elog(ERROR, "invalid after-trigger event code: %d",
+				 (event & TRIGGER_EVENT_OPMASK));
 			tgtype_event = 0;	/* keep compiler quiet */
 			break;
 	}
 
 	if (!(relkind == RELKIND_FOREIGN_TABLE && row_trigger))
-		new_event.ate_flags = (row_trigger && event == TRIGGER_EVENT_UPDATE) ?
+		new_event.ate_flags = (row_trigger && TRIGGER_FIRED_BY_UPDATE(event)) ?
 			AFTER_TRIGGER_2CTID : AFTER_TRIGGER_1CTID;
 	/* else, we'll initialize ate_flags for each trigger */
 
@@ -5652,14 +5777,25 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
 	for (i = 0; i < trigdesc->numtriggers; i++)
 	{
 		Trigger    *trigger = &trigdesc->triggers[i];
+		Tuplestorestate *oldstore,
+				   *newstore;
+
+		if (transition_capture)
+		{
+			Assert(transition_capture->tcs_private);
+			oldstore = transition_capture->tcs_private->old_tuplestore;
+			newstore = transition_capture->tcs_private->new_tuplestore;
+		}
+		else
+			oldstore = newstore = NULL;
 
 		if (!TRIGGER_TYPE_MATCHES(trigger->tgtype,
 								  tgtype_level,
 								  TRIGGER_TYPE_AFTER,
 								  tgtype_event))
 			continue;
-		if (!TriggerEnabled(estate, relinfo, trigger, event,
-							modifiedCols, oldslot, newslot))
+		if (!TriggerEnabled(estate, relinfo, trigger, event, modifiedCols,
+							oldslot, newslot, oldstore, newstore))
 			continue;
 
 		if (relkind == RELKIND_FOREIGN_TABLE && row_trigger)
@@ -5789,6 +5925,374 @@ before_stmt_triggers_fired(Oid relid, CmdType cmdType)
 	return result;
 }
 
+
+
+/*
+ * WhenQueryCache_Init
+ *
+ * Initialise cache for WHEN expression query plans.
+ */
+static void
+WhenQueryCache_Init(void)
+{
+	HASHCTL		ctl;
+
+	ctl.keysize = sizeof(Oid);
+	ctl.entrysize = sizeof(WhenQueryCacheEntry);
+	WhenQueryCache = hash_create("Trigger WHEN expression plan cache",
+								 WHENQUERYCACHE_INIT_SIZE,
+								 &ctl, HASH_ELEM | HASH_BLOBS);
+}
+
+/*
+ * WhenQueryCache_FetchPlan
+ *
+ * Retrieve a plan for the WHEN expression query from the cache.
+ */
+static SPIPlanPtr
+WhenQueryCache_FetchPlan(Oid key)
+{
+	WhenQueryCacheEntry	*entry;
+
+	/* Initialise if required */
+	if (!WhenQueryCache)
+		WhenQueryCache_Init();
+
+	entry = (WhenQueryCacheEntry *) hash_search(WhenQueryCache,
+												(void *) &key,
+												HASH_FIND, NULL);
+	if (entry == NULL)
+		return NULL;
+
+	/*
+	 * Check that the plan is still valid; else, discard it and allow it to
+	 * be re-planned.
+	 *
+	 * We can use SPI_plan_is_valid as we have a lock on the relation being
+	 * triggered.  That is the only relation (other than the transition
+	 * tables, of course) that can be referenced in the query.
+	 */
+	if (entry->plan && !SPI_plan_is_valid(entry->plan))
+	{
+		SPI_freeplan(entry->plan);
+		entry->plan = NULL;
+
+		return NULL;
+	}
+
+	return entry->plan;
+}
+
+
+/*
+ * WhenQueryCache_StorePlan
+ *
+ * Store a plan for a WHEN expression query in the cache.
+ */
+static void
+WhenQueryCache_StorePlan(Oid key, SPIPlanPtr plan)
+{
+	WhenQueryCacheEntry	*entry;
+	bool				found;
+
+	/* Initialise if required */
+	if (!WhenQueryCache)
+		WhenQueryCache_Init();
+
+	entry = (WhenQueryCacheEntry *) hash_search(WhenQueryCache,
+												(void *) &key,
+												HASH_ENTER, &found);
+	Assert(!found || entry->plan == NULL);
+	entry->plan = plan;
+}
+
+
+/*
+ * makeWhenExprParseState  - create a parse state suitable for transforming
+ * a WHEN expression.
+ */
+static ParseState *
+makeWhenExprParseState(const char *queryString, int16 tgtype, Relation rel,
+					   char *oldtablename, char *newtablename)
+{
+	ParseState	*pstate;
+
+	pstate = make_parsestate(NULL);
+	pstate->p_sourcetext = queryString;
+	pstate->p_queryEnv = create_queryEnv();
+
+	/*
+	 * In ROW triggers, the references to NEW or OLD are treated as tuple
+ 	 * variables, whereas in STATEMENT triggers they are treated as relation
+	 * variables.
+	 *
+	 * That dictates a slightly different parser state.  In ROW triggers we
+	 * need to give the parser a range table that contains entries for the
+	 * special var numbers PRS2_OLD_VARNO and PRS2_NEW_VARNO.  No such range
+	 * table is required for STATEMENT triggers as the expression itself
+	 * will have defined a range table; but, we do need to attach ephemeral
+	 * relations into the query environment to satisfy any OLD and NEW
+	 * references.
+	 */
+	if (TRIGGER_FOR_ROW(tgtype))
+	{
+		ParseNamespaceItem	*nsitem;
+
+		/*
+		 * Set up nsitems for OLD and NEW references.
+		 *
+		 * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+		 */
+		nsitem = addRangeTableEntryForRelation(pstate, rel,
+											   AccessShareLock,
+											   makeAlias("old", NIL),
+											   false, false);
+		addNSItemToQuery(pstate, nsitem, false, true, true);
+		nsitem = addRangeTableEntryForRelation(pstate, rel,
+											   AccessShareLock,
+											   makeAlias("new", NIL),
+											   false, false);
+		addNSItemToQuery(pstate, nsitem, false, true, true);
+	}
+	else
+	{
+		/*
+		 * Set up QueryEnvironment for OLD and NEW references.
+		 *
+		 * This allows subqueries to resolve column names etc.
+		 */
+		if (oldtablename != NULL)
+		{
+			EphemeralNamedRelation oldenr;
+
+			oldenr = makeENR(oldtablename, RelationGetRelid(rel), NULL);
+			register_ENR(pstate->p_queryEnv, oldenr);
+		}
+		if (newtablename != NULL)
+		{
+			EphemeralNamedRelation newenr;
+
+			newenr = makeENR(newtablename, RelationGetRelid(rel), NULL);
+			register_ENR(pstate->p_queryEnv, newenr);
+		}
+	}
+
+	return pstate;
+}
+
+
+/*
+ * freeWhenExprParseState - free parse state created by makeWhenExprParseState.
+ */
+static void
+freeWhenExprParseState(ParseState *pstate, char *oldtablename, char *newtablename)
+{
+	EphemeralNamedRelation enr;
+
+	Assert(pstate != NULL);
+	Assert(pstate->p_queryEnv != NULL);
+
+	/* Old ENR */
+	if (oldtablename != NULL &&
+		get_ENR(pstate->p_queryEnv, oldtablename) != NULL)
+	{
+		enr = get_ENR(pstate->p_queryEnv, oldtablename);
+		unregister_ENR(pstate->p_queryEnv, oldtablename);
+		pfree(enr);
+	}
+
+	/* New ENR */
+	if (newtablename != NULL &&
+		get_ENR(pstate->p_queryEnv, newtablename) != NULL)
+	{
+		enr = get_ENR(pstate->p_queryEnv, newtablename);
+		unregister_ENR(pstate->p_queryEnv, newtablename);
+		pfree(enr);
+	}
+
+	pfree(pstate->p_queryEnv);
+	free_parsestate(pstate);
+}
+
+
+/*
+ * validateWhenExpr - check WHEN expression is defined appropriately.
+ *
+ * A WHEN expression must only refer to certain tables or variables,
+ * depending on if it is a ROW or STATEMENT trigger, if it is BEFORE or
+ * AFTER INSERT, etc.
+ */
+static void
+validateWhenExpr(ParseState	*pstate, Node *whenClause, Relation rel,
+				 int16 tgtype, char *oldtablename, char *newtablename)
+{
+	ValidateWhenExprContext	context;
+
+	/* Setup the walker context and validate the expression. */
+	context.pstate = pstate;
+	context.rel = rel;
+	context.tgtype = tgtype;
+	context.oldtablename = oldtablename;
+	context.newtablename = newtablename;
+	context.rtable = pstate->p_rtable;
+
+	(void) validateWhenExpr_Walker(whenClause, (void *) &context);
+}
+
+
+/*
+ * validateWhenExpr_Walker - logic for validateWhenExpr.
+ */
+static bool
+validateWhenExpr_Walker(Node *node, ValidateWhenExprContext *ctx)
+{
+	if (node == NULL)
+		return false;
+	else if (IsA(node, Var))
+	{
+		Var				*var;
+		RangeTblEntry	*rte;
+		bool			oldvar, newvar;
+
+		var = castNode(Var, node);
+		rte = rt_fetch(var->varno, ctx->rtable);
+
+		/*
+		 * We need to figure out if the Var is for the OLD or NEW table so
+		 * that we can check for certain illegal references.
+		 *
+		 * If the Var is for the table that is being triggered against, that
+		 * is allowed, but if the Var is for some other table, then we
+		 * disallow that.  But, we do that when visiting the RangeTblRef
+		 * rather than here.
+		 */
+		oldvar = TRIGGER_FOR_ROW(ctx->tgtype)
+				 ? var->varno == PRS2_OLD_VARNO
+				 : rte->rtekind == RTE_NAMEDTUPLESTORE &&
+				   ctx->oldtablename != NULL &&
+				   strcmp(rte->enrname, ctx->oldtablename) == 0;
+		newvar = TRIGGER_FOR_ROW(ctx->tgtype)
+				 ? var->varno == PRS2_NEW_VARNO
+				 : rte->rtekind == RTE_NAMEDTUPLESTORE &&
+				   ctx->newtablename != NULL &&
+				   strcmp(rte->enrname, ctx->newtablename) == 0;
+
+		/*
+		 * Check Vars from the OLD table.
+		 */
+		if (oldvar && TRIGGER_FOR_INSERT(ctx->tgtype))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
+					 parser_errposition(ctx->pstate, var->location)));
+
+		/*
+		 * Check Vars from the NEW table.
+		 */
+		if (newvar && TRIGGER_FOR_DELETE(ctx->tgtype))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
+					 parser_errposition(ctx->pstate, var->location)));
+
+		if (newvar && TRIGGER_FOR_BEFORE(ctx->tgtype) && var->varattno < 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
+					 parser_errposition(ctx->pstate, var->location)));
+
+		if (newvar && TRIGGER_FOR_BEFORE(ctx->tgtype) &&
+			var->varattno == 0 &&
+			RelationGetDescr(ctx->rel)->constr &&
+			RelationGetDescr(ctx->rel)->constr->has_generated_stored)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+					 errdetail("A whole-row reference is used and the table contains generated columns."),
+					 parser_errposition(ctx->pstate, var->location)));
+
+		if (newvar && TRIGGER_FOR_BEFORE(ctx->tgtype) &&
+			var->varattno > 0 &&
+			TupleDescAttr(RelationGetDescr(ctx->rel), var->varattno - 1)->attgenerated)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+					 errdetail("Column \"%s\" is a generated column.",
+							   NameStr(TupleDescAttr(RelationGetDescr(ctx->rel), var->varattno - 1)->attname)),
+					 parser_errposition(ctx->pstate, var->location)));
+
+		/* fall through to recurse */
+	}
+	else if (IsA(node, RangeTblRef))
+	{
+		RangeTblRef		*ref;
+		RangeTblEntry	*rte;
+
+		ref = castNode(RangeTblRef, node);
+		rte = rt_fetch(ref->rtindex, ctx->rtable);
+
+		if (rte->rtekind == RTE_RELATION &&
+			rte->relid != RelationGetRelid(ctx->rel))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("trigger WHEN condition cannot contain references to other relations")));
+		/* fall through to recurse */
+	}
+	else if (IsA(node, Query))
+	{
+		Query	*query;
+		List	*rtable;
+		bool	result;
+
+		if (TRIGGER_FOR_ROW(ctx->tgtype))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					 errmsg("row trigger's WHEN condition cannot use subqueries")));
+
+		/* Statement triggers allow subqueries, so traverse into it. */
+		query = castNode(Query, node);
+		rtable = ctx->rtable;
+
+		ctx->rtable = query->rtable;
+		result = query_tree_walker(query, validateWhenExpr_Walker, ctx, 0);
+		ctx->rtable = rtable;
+
+		return result;
+	}
+	return expression_tree_walker(node, validateWhenExpr_Walker, ctx);
+}
+
+
+/*
+ * makeENR - creates an EphemeralNamedRelation with the given name and
+ * relation.
+ */
+static EphemeralNamedRelation
+makeENR(char *name, Oid relid, Tuplestorestate *data)
+{
+	int64 					tuple_count;
+	EphemeralNamedRelation	enr;
+
+	/*
+	 * We may have been called during analysis of the expression, rather
+	 * than during execution, in which case we won't have any actual data.
+	 */
+	if (data == NULL)
+		tuple_count = 0;
+	else
+		tuple_count = tuplestore_tuple_count(data);
+
+	enr = (EphemeralNamedRelation) palloc(sizeof(EphemeralNamedRelationData));
+	enr->reldata = (void *) data;
+	enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+	enr->md.reliddesc = relid;
+	enr->md.name = name;
+	enr->md.tupdesc = NULL;
+	enr->md.enrtuples = (double) tuple_count;
+
+	return enr;
+}
+
 /*
  * If we previously queued a set of AFTER STATEMENT triggers for the given
  * relation + operation, and they've not been fired yet, cancel them.  The
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f928c32311..768e2f5f54 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1725,6 +1725,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
 		case EXPR_KIND_RETURNING:
 		case EXPR_KIND_VALUES:
 		case EXPR_KIND_VALUES_SINGLE:
+		case EXPR_KIND_TRIGGER_WHEN:
 		case EXPR_KIND_CYCLE_MARK:
 			/* okay */
 			break;
@@ -1751,9 +1752,6 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
 		case EXPR_KIND_EXECUTE_PARAMETER:
 			err = _("cannot use subquery in EXECUTE parameter");
 			break;
-		case EXPR_KIND_TRIGGER_WHEN:
-			err = _("cannot use subquery in trigger WHEN condition");
-			break;
 		case EXPR_KIND_PARTITION_BOUND:
 			err = _("cannot use subquery in partition bound");
 			break;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 84ad62caea..92a341cee6 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1002,53 +1002,15 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 	if (!isnull)
 	{
 		Node	   *qual;
-		char		relkind;
 		deparse_context context;
-		deparse_namespace dpns;
-		RangeTblEntry *oldrte;
-		RangeTblEntry *newrte;
 
 		appendStringInfoString(&buf, "WHEN (");
 
 		qual = stringToNode(TextDatumGetCString(value));
 
-		relkind = get_rel_relkind(trigrec->tgrelid);
-
-		/* Build minimal OLD and NEW RTEs for the rel */
-		oldrte = makeNode(RangeTblEntry);
-		oldrte->rtekind = RTE_RELATION;
-		oldrte->relid = trigrec->tgrelid;
-		oldrte->relkind = relkind;
-		oldrte->rellockmode = AccessShareLock;
-		oldrte->alias = makeAlias("old", NIL);
-		oldrte->eref = oldrte->alias;
-		oldrte->lateral = false;
-		oldrte->inh = false;
-		oldrte->inFromCl = true;
-
-		newrte = makeNode(RangeTblEntry);
-		newrte->rtekind = RTE_RELATION;
-		newrte->relid = trigrec->tgrelid;
-		newrte->relkind = relkind;
-		newrte->rellockmode = AccessShareLock;
-		newrte->alias = makeAlias("new", NIL);
-		newrte->eref = newrte->alias;
-		newrte->lateral = false;
-		newrte->inh = false;
-		newrte->inFromCl = true;
-
-		/* Build two-element rtable */
-		memset(&dpns, 0, sizeof(dpns));
-		dpns.rtable = list_make2(oldrte, newrte);
-		dpns.subplans = NIL;
-		dpns.ctes = NIL;
-		dpns.appendrels = NULL;
-		set_rtable_names(&dpns, NIL, NULL);
-		set_simple_column_names(&dpns);
-
-		/* Set up context with one-deep namespace stack */
+		/* Set up context */
 		context.buf = &buf;
-		context.namespaces = list_make1(&dpns);
+		context.namespaces = NIL;
 		context.windowClause = NIL;
 		context.windowTList = NIL;
 		context.varprefix = true;
@@ -1058,6 +1020,54 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
 		context.special_exprkind = EXPR_KIND_NONE;
 		context.appendparents = NULL;
 
+		/* For ROW triggers we need to build the OLD and NEW RTEs for the rel.
+		 * This isn't necessary for STATEMENT triggers as their WHEN expression
+		 * will already have a range table defined in the EXISTS expressions. */
+		if (TRIGGER_FOR_ROW(trigrec->tgtype))
+		{
+			char				relkind;
+			deparse_namespace	dpns;
+			RangeTblEntry		*oldrte;
+			RangeTblEntry		*newrte;
+
+			relkind = get_rel_relkind(trigrec->tgrelid);
+
+			/* Build minimal OLD and NEW RTEs for the rel */
+			oldrte = makeNode(RangeTblEntry);
+			oldrte->rtekind = RTE_RELATION;
+			oldrte->relid = trigrec->tgrelid;
+			oldrte->relkind = relkind;
+			oldrte->rellockmode = AccessShareLock;
+			oldrte->alias = makeAlias("old", NIL);
+			oldrte->eref = oldrte->alias;
+			oldrte->lateral = false;
+			oldrte->inh = false;
+			oldrte->inFromCl = true;
+
+			newrte = makeNode(RangeTblEntry);
+			newrte->rtekind = RTE_RELATION;
+			newrte->relid = trigrec->tgrelid;
+			newrte->relkind = relkind;
+			newrte->rellockmode = AccessShareLock;
+			newrte->alias = makeAlias("new", NIL);
+			newrte->eref = newrte->alias;
+			newrte->lateral = false;
+			newrte->inh = false;
+			newrte->inFromCl = true;
+
+			/* Build two-element rtable */
+			memset(&dpns, 0, sizeof(dpns));
+			dpns.rtable = list_make2(oldrte, newrte);
+			dpns.subplans = NIL;
+			dpns.ctes = NIL;
+			dpns.appendrels = NULL;
+			set_rtable_names(&dpns, NIL, NULL);
+			set_simple_column_names(&dpns);
+
+			/* Make the deparse context aware */
+			context.namespaces = list_make1(&dpns);
+		}
+
 		get_rule_expr(qual, &context, false);
 
 		appendStringInfoString(&buf, ") ");
@@ -4203,7 +4213,8 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte,
 	 * get_rte_attribute_name, except that it's important to disregard dropped
 	 * columns.  We put NULL into the array for a dropped column.
 	 */
-	if (rte->rtekind == RTE_RELATION)
+	if (rte->rtekind == RTE_RELATION ||
+		rte->rtekind == RTE_NAMEDTUPLESTORE)
 	{
 		/* Relation --- look to the system catalogs for up-to-date info */
 		Relation	rel;
@@ -4338,7 +4349,8 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte,
 	 * print if we changed anything OR if there were user-written column
 	 * aliases (since the latter would be part of the underlying "reality").
 	 */
-	if (rte->rtekind == RTE_RELATION)
+	if (rte->rtekind == RTE_RELATION ||
+		rte->rtekind == RTE_NAMEDTUPLESTORE)
 		colinfo->printaliases = changed_any;
 	else if (rte->rtekind == RTE_FUNCTION)
 		colinfo->printaliases = true;
@@ -10821,6 +10833,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
 			case RTE_CTE:
 				appendStringInfoString(buf, quote_identifier(rte->ctename));
 				break;
+			case RTE_NAMEDTUPLESTORE:
+				/* Ephemeral RTE */
+				appendStringInfo(buf, "%s", rte->enrname);
+				break;
 			default:
 				elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind);
 				break;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 7795a69490..3e358ecd62 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -440,7 +440,7 @@ typedef struct ResultRelInfo
 	/* cached lookup info for trigger functions */
 	FmgrInfo   *ri_TrigFunctions;
 
-	/* array of trigger WHEN expr states */
+	/* array of trigger WHEN expr states for FOR EACH ROW triggers */
 	ExprState **ri_TrigWhenExprs;
 
 	/* optional runtime measurements for triggers */
diff --git a/src/include/utils/reltrigger.h b/src/include/utils/reltrigger.h
index 7dc7699b5c..9509fa19a9 100644
--- a/src/include/utils/reltrigger.h
+++ b/src/include/utils/reltrigger.h
@@ -24,6 +24,7 @@ typedef struct Trigger
 {
 	Oid			tgoid;			/* OID of trigger (pg_trigger row) */
 	/* Remaining fields are copied from pg_trigger, see pg_trigger.h */
+	Oid 		tgrelid;
 	char	   *tgname;
 	Oid			tgfoid;
 	int16		tgtype;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index e8af9a9589..875e14606a 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -588,6 +588,70 @@ create trigger oid_unchanged_trig after update on table_with_oids
 update table_with_oids set a = a + 1;
 NOTICE:  trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW
 drop table table_with_oids;
+-- Test FOR EACH STATEMENT triggers with queries in their WHEN condition
+--
+-- disable other triggers
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_stmt_trig;
+SELECT a, b FROM main_table ORDER BY a, b;
+ a  | b  
+----+----
+  6 | 10
+ 21 | 20
+ 30 | 40
+ 31 | 10
+ 50 | 35
+ 50 | 60
+ 81 | 15
+    |   
+(8 rows)
+
+-- legal WHEN expressions
+CREATE TRIGGER after_insert AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (500 <= ANY(SELECT b FROM NEW))
+  EXECUTE PROCEDURE trigger_func('after_insert');
+INSERT INTO main_table (a, b) VALUES -- after_insert won't fire
+  (101, 498),
+  (102, 499);
+INSERT INTO main_table (a, b) VALUES -- after_insert will fire
+  (103, 501),
+  (104, -99);
+NOTICE:  trigger_func(after_insert) called: action = INSERT, when = AFTER, level = STATEMENT
+DROP TRIGGER after_insert ON main_table;
+CREATE TRIGGER after_delete AFTER DELETE ON main_table
+  REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT
+  WHEN (0 >= ANY(SELECT b FROM OLD))
+  EXECUTE PROCEDURE trigger_func('after_delete');
+DELETE FROM main_table WHERE a IN (101, 102); -- after delete won't fire
+DELETE FROM main_table WHERE a IN (103, 104); -- after delete will fire
+NOTICE:  trigger_func(after_delete) called: action = DELETE, when = AFTER, level = STATEMENT
+DROP TRIGGER after_delete ON main_table;
+CREATE TRIGGER after_update AFTER UPDATE ON main_table
+  REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (EXISTS (SELECT 1 FROM new JOIN old ON (new.a = old.a) WHERE new.a < 50 AND new.b < old.b))
+  EXECUTE PROCEDURE trigger_func('after_update');
+UPDATE main_table SET b = (b + 1); -- after_update won't fire
+UPDATE main_table SET b = (b - 1); -- after_update will fire
+NOTICE:  trigger_func(after_update) called: action = UPDATE, when = AFTER, level = STATEMENT
+DROP TRIGGER after_update ON main_table;
+-- illegal WHEN expressions
+CREATE TABLE other_table (n INTEGER NOT NULL PRIMARY KEY);
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (1 = ANY (SELECT a FROM NEW INTERSECT SELECT n FROM other_table))
+  EXECUTE PROCEDURE trigger_func('error_due_to_other_table');
+ERROR:  trigger WHEN condition cannot contain references to other relations
+DROP TABLE other_table;
+-- re-enable other triggers
+ALTER TABLE main_table ENABLE TRIGGER after_upd_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+--
+-- Done testing FOR EACH STATEMENT with queries in the WHEN condition
 -- Test column-level triggers
 DROP TRIGGER after_upd_row_trig ON main_table;
 CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
@@ -700,7 +764,7 @@ LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0)
 CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
 FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
 EXECUTE PROCEDURE trigger_func('error_stmt_when');
-ERROR:  statement trigger's WHEN condition cannot reference column values
+ERROR:  missing FROM-clause entry for table "old"
 LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
                                  ^
 -- check dependency restrictions
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index b50f500045..91047361ca 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -369,6 +369,63 @@ create trigger oid_unchanged_trig after update on table_with_oids
 update table_with_oids set a = a + 1;
 drop table table_with_oids;
 
+-- Test FOR EACH STATEMENT triggers with queries in their WHEN condition
+--
+
+-- disable other triggers
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_stmt_trig;
+
+SELECT a, b FROM main_table ORDER BY a, b;
+
+-- legal WHEN expressions
+CREATE TRIGGER after_insert AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (500 <= ANY(SELECT b FROM NEW))
+  EXECUTE PROCEDURE trigger_func('after_insert');
+INSERT INTO main_table (a, b) VALUES -- after_insert won't fire
+  (101, 498),
+  (102, 499);
+INSERT INTO main_table (a, b) VALUES -- after_insert will fire
+  (103, 501),
+  (104, -99);
+DROP TRIGGER after_insert ON main_table;
+
+CREATE TRIGGER after_delete AFTER DELETE ON main_table
+  REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT
+  WHEN (0 >= ANY(SELECT b FROM OLD))
+  EXECUTE PROCEDURE trigger_func('after_delete');
+DELETE FROM main_table WHERE a IN (101, 102); -- after delete won't fire
+DELETE FROM main_table WHERE a IN (103, 104); -- after delete will fire
+DROP TRIGGER after_delete ON main_table;
+
+CREATE TRIGGER after_update AFTER UPDATE ON main_table
+  REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (EXISTS (SELECT 1 FROM new JOIN old ON (new.a = old.a) WHERE new.a < 50 AND new.b < old.b))
+  EXECUTE PROCEDURE trigger_func('after_update');
+UPDATE main_table SET b = (b + 1); -- after_update won't fire
+UPDATE main_table SET b = (b - 1); -- after_update will fire
+DROP TRIGGER after_update ON main_table;
+
+-- illegal WHEN expressions
+CREATE TABLE other_table (n INTEGER NOT NULL PRIMARY KEY);
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+  REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+  WHEN (1 = ANY (SELECT a FROM NEW INTERSECT SELECT n FROM other_table))
+  EXECUTE PROCEDURE trigger_func('error_due_to_other_table');
+DROP TABLE other_table;
+
+-- re-enable other triggers
+ALTER TABLE main_table ENABLE TRIGGER after_upd_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+
+--
+-- Done testing FOR EACH STATEMENT with queries in the WHEN condition
+
 -- Test column-level triggers
 DROP TRIGGER after_upd_row_trig ON main_table;
 
-- 
2.31.1