Hi hackers,
Attached is a patch for supporting queries in the WHEN expression of
statement triggers. It is restricted so that the expression can
reference only the transition tables and the table to which the trigger
is attached. This seemed to make the most sense in that it follows what
you can do in the per row triggers. I did have a look in the standards
document about triggers, and couldn't see any restrictions mentioned,
but nevertheless thought it made most sense.
One possibility controversial aspect is that the patch doesn't use SPI
to evaluate the expression; it constructs a Query instead and passes it
to the executor. Don't know what people's thoughts are on doing that?
-Joe
From cdc8f5826fc5b0bc576c79c40740ced2400811a4 Mon Sep 17 00:00:00 2001
From: Joe Wildish <j...@sql.dev>
Date: Thu, 16 Jul 2020 23:04:55 +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 +-
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 +++
7 files changed, 696 insertions(+), 182 deletions(-)
diff --git a/doc/src/sgml/ref/create_trigger.sgml
b/doc/src/sgml/ref/create_trigger.sgml
index 289dd1d9da..faba940b66 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -152,13 +152,14 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable
class="parameter">name</replaceable>
</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>
@@ -367,23 +368,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/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 672fccff5b..bfb6bad717 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -35,12 +35,15 @@
#include "commands/defrem.h"
#include "commands/trigger.h"
#include "executor/executor.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"
@@ -58,6 +61,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"
@@ -69,6 +73,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
@@ -91,7 +106,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,
@@ -104,6 +120,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);
/*
@@ -548,29 +571,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,
@@ -581,79 +628,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)
@@ -1042,8 +1043,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,
@@ -2160,7 +2185,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;
@@ -2214,7 +2239,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)
@@ -2270,10 +2295,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);
@@ -2305,7 +2331,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)
@@ -2374,7 +2400,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;
@@ -2469,7 +2495,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;
@@ -2502,6 +2528,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))
@@ -2518,7 +2545,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);
}
@@ -2552,7 +2579,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;
@@ -2609,7 +2636,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;
@@ -2718,7 +2745,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)
@@ -2789,6 +2816,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);
@@ -2814,7 +2842,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);
@@ -2851,7 +2879,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)
@@ -2917,7 +2945,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;
@@ -3065,7 +3093,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)
@@ -3104,8 +3133,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;
@@ -3157,6 +3188,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 should never return
NULL.
+ */
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "WHEN", BOOLOID,
-1, 0);
+ tuplestore_gettupleslot(store, true, false, whenslot);
+ when = DatumGetBool(slot_getattr(whenslot, 1, &isnull));
+ tuplestore_end(store);
+ Assert(!isnull);
+
+ /*
+ * 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 (!(when && !isnull))
+ return false;
+ }
+
return true;
}
@@ -5403,14 +5537,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;
@@ -5436,8 +5570,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;
@@ -5475,10 +5609,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;
}
@@ -5494,7 +5628,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;
@@ -5511,8 +5645,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:
@@ -5530,8 +5664,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:
@@ -5549,8 +5683,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:
@@ -5561,13 +5695,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 */
@@ -5576,14 +5711,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)
@@ -5713,6 +5859,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 f69976cc8c..48a8900e6d 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1897,6 +1897,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:
@@ -1919,9 +1920,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 2cbcb4b85e..50c28300b3 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -997,53 +997,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;
@@ -1053,6 +1015,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, ") ");
@@ -10228,6 +10238,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 6f96b31fb4..7930e91f4a 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -427,7 +427,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 5e76b3a47e..7db185070b 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -534,6 +534,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
@@ -646,7 +717,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 e228d0a8a5..3444f2c534 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -342,6 +342,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.27.0
