Hi Surafel,
On 3 Sep 2020, at 19:22, Surafel Temesgen wrote:
This is my review of your patch
Thanks for the review.
subqueries in row trigger's is not supported in your patch so the the
documentation have to reflect it
It is still the case that the documentation says this. But, that may
have been unclear as the documentation wouldn't compile (as you noted),
so it wasn't possible to read it in the rendered form.
+ </literal>UPDATE</literal> triggers are able to refer to both
</literal>OLD</literal>
+ and <literal>NEW</literal>
Opening and ending tag mismatch on UPDATE and OLD literal so
documentation
build fails and please update the documentation on server programming
section too
Fixed.
I've also amended the server programming section to accurately reflect
how WHEN conditions can be used.
Instead of planning every time the trigger fire I suggest to store
plan or
prepared statement node so planning time can be saved
Yes, that would make sense. I'll look in to what needs to be done.
Do you know if there are other areas of the code that cache plans that
could act as a guide as to how best to achieve it?
There are server crash on the following sequence of command
...
INSERT INTO main_table (a, b) VALUES
(101, 498),
(102, 499);
server crashed
Thanks. It was an incorrect Assert about NULL returns. Fixed.
-Joe
From 56d010c925db41ffe689044ba215640600976748 Mon Sep 17 00:00:00 2001
From: Joe Wildish <j...@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
