From 7fc0f06221d22632daa3ff8b70919b43e8a242ca Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 20 Nov 2009 20:38:12 +0000 Subject: [PATCH] Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to be checked to determine whether the trigger should be fired. For BEFORE triggers this is mostly a matter of spec compliance; but for AFTER triggers it can provide a noticeable performance improvement, since queuing of a deferred trigger event and re-fetching of the row(s) at end of statement can be short-circuited if the trigger does not need to be fired. Takahiro Itagaki, reviewed by KaiGai Kohei. --- doc/src/sgml/catalogs.sgml | 11 +- doc/src/sgml/ref/create_constraint.sgml | 19 +- doc/src/sgml/ref/create_trigger.sgml | 112 ++++++++- doc/src/sgml/trigger.sgml | 22 +- src/backend/catalog/index.c | 5 +- src/backend/commands/copy.c | 11 +- src/backend/commands/tablecmds.c | 13 +- src/backend/commands/trigger.c | 311 ++++++++++++++++++++++-- src/backend/executor/execMain.c | 6 +- src/backend/executor/execQual.c | 55 +++-- src/backend/executor/execUtils.c | 3 +- src/backend/executor/nodeModifyTable.c | 12 +- src/backend/nodes/copyfuncs.c | 3 +- src/backend/nodes/equalfuncs.c | 3 +- src/backend/parser/gram.y | 29 ++- src/backend/tcop/utility.c | 4 +- src/backend/utils/adt/ruleutils.c | 67 ++++- src/bin/pg_dump/pg_dump.c | 11 +- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_trigger.h | 8 +- src/include/catalog/toasting.h | 3 +- src/include/commands/trigger.h | 4 +- src/include/nodes/execnodes.h | 7 +- src/include/nodes/parsenodes.h | 3 +- src/include/utils/rel.h | 3 +- src/test/regress/expected/triggers.out | 108 ++++++++ src/test/regress/sql/triggers.sql | 46 ++++ 27 files changed, 783 insertions(+), 100 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 174dd0ee81..f2959af526 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -4756,6 +4756,15 @@ Argument strings to pass to trigger, each NULL-terminated + + + tgqual + text + + Expression tree (in nodeToString() + representation) for the trigger's WHEN condition, or NULL + if none + diff --git a/doc/src/sgml/ref/create_constraint.sgml b/doc/src/sgml/ref/create_constraint.sgml index dbf4d679f2..7da8831856 100644 --- a/doc/src/sgml/ref/create_constraint.sgml +++ b/doc/src/sgml/ref/create_constraint.sgml @@ -1,5 +1,5 @@ @@ -27,6 +27,7 @@ CREATE CONSTRAINT TRIGGER name [ FROM referenced_table_name ] { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } } FOR EACH ROW + [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments ) @@ -109,6 +110,22 @@ CREATE CONSTRAINT TRIGGER name + + condition + + + A Boolean expression that determines whether the trigger function + will actually be executed. This acts the same as in . + Note in particular that evaluation of the WHEN + condition is not deferred, but occurs immediately after the row + update operation is performed. If the condition does not evaluate + to true then the trigger is not queued for deferred + execution. + + + + function_name diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 04f681f030..78f5977999 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] + [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments ) @@ -72,6 +73,16 @@ CREATE TRIGGER name { BEFORE | AFTE FOR EACH STATEMENT. + + Also, a trigger definition can specify a boolean WHEN + condition, which will be tested to see whether the trigger should + be fired. In row-level triggers the WHEN condition can + examine the old and/or new values of columns of the row. Statement-level + triggers can also have WHEN conditions, although the feature + is not so useful for them since the condition cannot refer to any values + in the table. + + If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. @@ -159,6 +170,31 @@ UPDATE OF column_name1 [, column_name2 + + condition + + + A Boolean expression that determines whether the trigger function + will actually be executed. If WHEN is specified, the + function will only be called if the condition returns true. + In FOR EACH ROW triggers, the WHEN + condition can refer to columns of the old and/or new row values + by writing OLD.column_name or + NEW.column_name respectively. + Of course, INSERT triggers cannot refer to OLD + and DELETE triggers cannot refer to NEW. + + + + Currently, WHEN expressions cannot contain + subqueries. + + + + function_name @@ -213,6 +249,29 @@ UPDATE OF column_name1 [, column_name2 + + In a BEFORE trigger, the WHEN condition is + evaluated just before the function is or would be executed, so using + WHEN is not materially different from testing the same + condition at the beginning of the trigger function. Note in particular + that the NEW row seen by the condition is the current value, + as possibly modified by earlier triggers. Also, a BEFORE + trigger's WHEN condition is not allowed to examine the + system columns of the NEW row (such as oid), + because those won't have been set yet. + + + + In an AFTER trigger, the WHEN condition is + evaluated just after the row update occurs, and it determines whether an + event is queued to fire the trigger at the end of statement. So when an + AFTER trigger's WHEN condition does not return + true, it is not necessary to queue an event nor to re-fetch the row at end + of statement. This can result in significant speedups in statements that + modify many rows, if the trigger only needs to be fired for a few of the + rows. + + In PostgreSQL versions before 7.3, it was necessary to declare trigger functions as returning the placeholder @@ -223,11 +282,56 @@ UPDATE OF column_name1 [, column_name2 - + Examples - contains a complete example. + Execute the function check_account_update whenever + a row of the table accounts is about to be updated: + + +CREATE TRIGGER check_update + BEFORE UPDATE ON accounts + FOR EACH ROW + EXECUTE PROCEDURE check_account_update(); + + + The same, but only execute the function if column balance + is specified as a target in the UPDATE command: + + +CREATE TRIGGER check_update + BEFORE UPDATE OF balance ON accounts + FOR EACH ROW + EXECUTE PROCEDURE check_account_update(); + + + This form only executes the function if column balance + has in fact changed value: + + +CREATE TRIGGER check_update + BEFORE UPDATE ON accounts + FOR EACH ROW + WHEN (OLD.balance IS DISTINCT FROM NEW.balance) + EXECUTE PROCEDURE check_account_update(); + + + Call a function to log updates of accounts, but only if + something changed: + + +CREATE TRIGGER log_update + AFTER UPDATE ON accounts + FOR EACH ROW + WHEN (OLD.* IS DISTINCT FROM NEW.*) + EXECUTE PROCEDURE log_account_update(); + + + + + contains a complete example of a trigger + function written in C. @@ -258,7 +362,7 @@ UPDATE OF column_name1 [, column_name2PostgreSQL only allows the execution of a user-defined function for the triggered action. The standard allows the execution of a number of other SQL commands, such as - CREATE TABLE as the triggered action. This + CREATE TABLE, as the triggered action. This limitation is not hard to work around by creating a user-defined function that executes the desired commands. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 60977cbd56..2c2ec89b02 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -1,4 +1,4 @@ - + Triggers @@ -140,6 +140,25 @@ triggers are not fired. + + A trigger definition can also specify a boolean WHEN + condition, which will be tested to see whether the trigger should + be fired. In row-level triggers the WHEN condition can + examine the old and/or new values of columns of the row. (Statement-level + triggers can also have WHEN conditions, although the feature + is not so useful for them.) In a before trigger, the WHEN + condition is evaluated just before the function is or would be executed, + so using WHEN is not materially different from testing the + same condition at the beginning of the trigger function. However, in + an after trigger, the WHEN condition is evaluated just after + the row update occurs, and it determines whether an event is queued to + fire the trigger at the end of statement. So when an after trigger's + WHEN condition does not return true, it is not necessary + to queue an event nor to re-fetch the row at end of statement. This + can result in significant speedups in statements that modify many + rows, if the trigger only needs to be fired for a few of the rows. + + Typically, row before triggers are used for checking or modifying the data that will be inserted or updated. For example, @@ -497,6 +516,7 @@ typedef struct Trigger int16 tgnattr; int16 *tgattr; char **tgargs; + char *tgqual; } Trigger; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 573b00216d..482d40542d 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.323 2009/10/14 22:14:21 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.324 2009/11/20 20:38:09 tgl Exp $ * * * INTERFACE ROUTINES @@ -793,12 +793,13 @@ index_create(Oid heapRelationId, trigger->row = true; trigger->events = TRIGGER_TYPE_INSERT | TRIGGER_TYPE_UPDATE; trigger->columns = NIL; + trigger->whenClause = NULL; trigger->isconstraint = true; trigger->deferrable = true; trigger->initdeferred = initdeferred; trigger->constrrel = NULL; - (void) CreateTrigger(trigger, conOid, indexRelationId, + (void) CreateTrigger(trigger, NULL, conOid, indexRelationId, isprimary ? "PK_ConstraintTrigger" : "Unique_ConstraintTrigger", false); diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 5d60df2873..4ad49409c5 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.317 2009/09/21 20:10:21 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.318 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1799,8 +1799,12 @@ CopyFrom(CopyState cstate) resultRelInfo->ri_RelationDesc = cstate->rel; resultRelInfo->ri_TrigDesc = CopyTriggerDesc(cstate->rel->trigdesc); if (resultRelInfo->ri_TrigDesc) + { resultRelInfo->ri_TrigFunctions = (FmgrInfo *) palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(FmgrInfo)); + resultRelInfo->ri_TrigWhenExprs = (List **) + palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(List *)); + } resultRelInfo->ri_TrigInstrument = NULL; ExecOpenIndices(resultRelInfo); @@ -1810,7 +1814,8 @@ CopyFrom(CopyState cstate) estate->es_result_relation_info = resultRelInfo; /* Set up a tuple slot too */ - slot = MakeSingleTupleTableSlot(tupDesc); + slot = ExecInitExtraTupleSlot(estate); + ExecSetSlotDescriptor(slot, tupDesc); econtext = GetPerTupleExprContext(estate); @@ -2198,7 +2203,7 @@ CopyFrom(CopyState cstate) pfree(defmap); pfree(defexprs); - ExecDropSingleTupleTableSlot(slot); + ExecResetTupleTable(estate->es_tupleTable, false); ExecCloseIndices(resultRelInfo); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3e0c8191c7..1de1950453 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.305 2009/11/04 12:24:23 heikki Exp $ + * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.306 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -5403,7 +5403,7 @@ validateForeignKeyConstraint(Constraint *fkconstraint, trig.tgconstraint = constraintOid; trig.tgdeferrable = FALSE; trig.tginitdeferred = FALSE; - /* we needn't fill in tgargs */ + /* we needn't fill in tgargs or tgqual */ /* * See if we can do it with a single LEFT JOIN query. A FALSE result @@ -5476,13 +5476,14 @@ CreateFKCheckTrigger(RangeVar *myRel, Constraint *fkconstraint, } fk_trigger->columns = NIL; + fk_trigger->whenClause = NULL; fk_trigger->isconstraint = true; fk_trigger->deferrable = fkconstraint->deferrable; fk_trigger->initdeferred = fkconstraint->initdeferred; fk_trigger->constrrel = fkconstraint->pktable; fk_trigger->args = NIL; - (void) CreateTrigger(fk_trigger, constraintOid, indexOid, + (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ @@ -5527,6 +5528,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, fk_trigger->row = true; fk_trigger->events = TRIGGER_TYPE_DELETE; fk_trigger->columns = NIL; + fk_trigger->whenClause = NULL; fk_trigger->isconstraint = true; fk_trigger->constrrel = myRel; switch (fkconstraint->fk_del_action) @@ -5563,7 +5565,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, } fk_trigger->args = NIL; - (void) CreateTrigger(fk_trigger, constraintOid, indexOid, + (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ @@ -5580,6 +5582,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, fk_trigger->row = true; fk_trigger->events = TRIGGER_TYPE_UPDATE; fk_trigger->columns = NIL; + fk_trigger->whenClause = NULL; fk_trigger->isconstraint = true; fk_trigger->constrrel = myRel; switch (fkconstraint->fk_upd_action) @@ -5616,7 +5619,7 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, } fk_trigger->args = NIL; - (void) CreateTrigger(fk_trigger, constraintOid, indexOid, + (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, "RI_ConstraintTrigger", false); } diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 885c34d834..b1f8a61748 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.256 2009/10/27 20:14:27 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.257 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -32,10 +32,14 @@ #include "miscadmin.h" #include "nodes/bitmapset.h" #include "nodes/makefuncs.h" +#include "optimizer/clauses.h" +#include "optimizer/var.h" +#include "parser/parse_clause.h" #include "parser/parse_func.h" #include "parser/parse_relation.h" #include "parser/parsetree.h" #include "pgstat.h" +#include "rewrite/rewriteManip.h" #include "storage/bufmgr.h" #include "tcop/utility.h" #include "utils/acl.h" @@ -65,21 +69,27 @@ static HeapTuple GetTupleForTrigger(EState *estate, ResultRelInfo *relinfo, ItemPointer tid, TupleTableSlot **newSlot); -static bool TriggerEnabled(Trigger *trigger, TriggerEvent event, - Bitmapset *modifiedCols); +static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo, + Trigger *trigger, TriggerEvent event, + Bitmapset *modifiedCols, + HeapTuple oldtup, HeapTuple newtup); static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata, int tgindx, FmgrInfo *finfo, Instrumentation *instr, MemoryContext per_tuple_context); -static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, - bool row_trigger, HeapTuple oldtup, HeapTuple newtup, +static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, + int event, bool row_trigger, + HeapTuple oldtup, HeapTuple newtup, List *recheckIndexes, Bitmapset *modifiedCols); /* * Create a trigger. Returns the OID of the created trigger. * + * queryString is the source text of the CREATE TRIGGER command. + * This must be supplied if a whenClause is specified, else it can be NULL. + * * constraintOid, if nonzero, says that this trigger is being created * internally to implement that constraint. A suitable pg_depend entry will * be made to link the trigger to that constraint. constraintOid is zero when @@ -101,7 +111,7 @@ static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, * but a foreign-key constraint. This is a kluge for backwards compatibility. */ Oid -CreateTrigger(CreateTrigStmt *stmt, +CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions) { @@ -109,6 +119,9 @@ CreateTrigger(CreateTrigStmt *stmt, int ncolumns; int2 *columns; int2vector *tgattr; + Node *whenClause; + List *whenRtable; + char *qual; Datum values[Natts_pg_trigger]; bool nulls[Natts_pg_trigger]; Relation rel; @@ -179,6 +192,120 @@ CreateTrigger(CreateTrigStmt *stmt, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("TRUNCATE FOR EACH ROW triggers are not supported"))); + /* + * Parse the WHEN clause, if any + */ + if (stmt->whenClause) + { + ParseState *pstate; + RangeTblEntry *rte; + List *varList; + ListCell *lc; + + /* Set up a pstate to parse with */ + pstate = make_parsestate(NULL); + pstate->p_sourcetext = queryString; + + /* + * Set up RTEs for OLD and NEW references. + * + * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2. + */ + rte = addRangeTableEntryForRelation(pstate, rel, + makeAlias("old", NIL), + false, false); + addRTEtoQuery(pstate, rte, false, true, true); + rte = addRangeTableEntryForRelation(pstate, rel, + makeAlias("new", NIL), + false, false); + addRTEtoQuery(pstate, rte, false, true, true); + + /* Transform expression. Copy to be sure we don't modify original */ + whenClause = transformWhereClause(pstate, + copyObject(stmt->whenClause), + "WHEN"); + + /* + * No subplans or aggregates, please + */ + if (pstate->p_hasSubLinks) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use subquery in trigger WHEN condition"))); + if (pstate->p_hasAggs) + ereport(ERROR, + (errcode(ERRCODE_GROUPING_ERROR), + errmsg("cannot use aggregate function in trigger WHEN condition"))); + if (pstate->p_hasWindowFuncs) + ereport(ERROR, + (errcode(ERRCODE_WINDOWING_ERROR), + errmsg("cannot use window function in trigger WHEN condition"))); + + /* + * Check for disallowed references to OLD/NEW. + * + * 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. + */ + varList = pull_var_clause(whenClause, PVC_REJECT_PLACEHOLDERS); + foreach(lc, varList) + { + Var *var = (Var *) lfirst(lc); + + switch (var->varno) + { + case PRS2_OLD_VARNO: + if (!TRIGGER_FOR_ROW(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("statement trigger's WHEN condition cannot reference column values"), + parser_errposition(pstate, var->location))); + if (TRIGGER_FOR_INSERT(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("INSERT trigger's WHEN condition cannot reference OLD values"), + parser_errposition(pstate, var->location))); + /* system columns are okay here */ + break; + case PRS2_NEW_VARNO: + if (!TRIGGER_FOR_ROW(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("statement trigger's WHEN condition cannot reference column values"), + parser_errposition(pstate, var->location))); + if (TRIGGER_FOR_DELETE(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("DELETE trigger's WHEN condition cannot reference NEW values"), + parser_errposition(pstate, var->location))); + if (var->varattno < 0 && TRIGGER_FOR_BEFORE(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"), + parser_errposition(pstate, var->location))); + 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 */ + whenRtable = pstate->p_rtable; + + qual = nodeToString(whenClause); + + free_parsestate(pstate); + } + else + { + whenClause = NULL; + whenRtable = NIL; + qual = NULL; + } + /* * Find and validate the trigger function. */ @@ -387,6 +514,12 @@ CreateTrigger(CreateTrigStmt *stmt, tgattr = buildint2vector(columns, ncolumns); values[Anum_pg_trigger_tgattr - 1] = PointerGetDatum(tgattr); + /* set tgqual if trigger has WHEN clause */ + if (qual) + values[Anum_pg_trigger_tgqual - 1] = CStringGetTextDatum(qual); + else + nulls[Anum_pg_trigger_tgqual - 1] = true; + tuple = heap_form_tuple(tgrel->rd_att, values, nulls); /* force tuple to have the desired OID */ @@ -495,6 +628,14 @@ CreateTrigger(CreateTrigStmt *stmt, } } + /* + * If it has a WHEN clause, add dependencies on objects mentioned in + * the expression (eg, functions, as well as any columns used). + */ + if (whenClause != NULL) + recordDependencyOnExpr(&myself, whenClause, whenRtable, + DEPENDENCY_NORMAL); + /* Keep lock on target rel until end of xact */ heap_close(rel, NoLock); @@ -1184,6 +1325,8 @@ RelationBuildTriggers(Relation relation) { Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup); Trigger *build; + Datum datum; + bool isnull; if (numtrigs >= maxtrigs) { @@ -1218,7 +1361,6 @@ RelationBuildTriggers(Relation relation) if (build->tgnargs > 0) { bytea *val; - bool isnull; char *p; val = DatumGetByteaP(fastgetattr(htup, @@ -1237,6 +1379,12 @@ RelationBuildTriggers(Relation relation) } else build->tgargs = NULL; + datum = fastgetattr(htup, Anum_pg_trigger_tgqual, + tgrel->rd_att, &isnull); + if (!isnull) + build->tgqual = TextDatumGetCString(datum); + else + build->tgqual = NULL; numtrigs++; } @@ -1396,6 +1544,8 @@ CopyTriggerDesc(TriggerDesc *trigdesc) newargs[j] = pstrdup(trigger->tgargs[j]); trigger->tgargs = newargs; } + if (trigger->tgqual) + trigger->tgqual = pstrdup(trigger->tgqual); trigger++; } @@ -1497,6 +1647,8 @@ FreeTriggerDesc(TriggerDesc *trigdesc) pfree(trigger->tgargs[trigger->tgnargs]); pfree(trigger->tgargs); } + if (trigger->tgqual) + pfree(trigger->tgqual); trigger++; } pfree(trigdesc->triggers); @@ -1520,6 +1672,11 @@ equalTriggerDescs(TriggerDesc *trigdesc1, TriggerDesc *trigdesc2) * * As of 7.3 we assume trigger set ordering is significant in the * comparison; so we just compare corresponding slots of the two sets. + * + * Note: comparing the stringToNode forms of the WHEN clauses means that + * parse column locations will affect the result. This is okay as long + * as this function is only used for detecting exact equality, as for + * example in checking for staleness of a cache entry. */ if (trigdesc1 != NULL) { @@ -1565,6 +1722,12 @@ equalTriggerDescs(TriggerDesc *trigdesc1, TriggerDesc *trigdesc2) for (j = 0; j < trig1->tgnargs; j++) if (strcmp(trig1->tgargs[j], trig2->tgargs[j]) != 0) return false; + if (trig1->tgqual == NULL && trig2->tgqual == NULL) + /* ok */ ; + else if (trig1->tgqual == NULL || trig2->tgqual == NULL) + return false; + else if (strcmp(trig1->tgqual, trig2->tgqual) != 0) + return false; } } else if (trigdesc2 != NULL) @@ -1687,7 +1850,8 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo) Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; @@ -1710,7 +1874,7 @@ ExecASInsertTriggers(EState *estate, ResultRelInfo *relinfo) TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, false, NULL, NULL, NIL, NULL); } @@ -1737,7 +1901,8 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, NULL, newtuple)) continue; LocTriggerData.tg_trigtuple = oldtuple = newtuple; @@ -1763,7 +1928,7 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, true, NULL, trigtuple, recheckIndexes, NULL); } @@ -1800,7 +1965,8 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; @@ -1823,7 +1989,7 @@ ExecASDeleteTriggers(EState *estate, ResultRelInfo *relinfo) TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, false, NULL, NULL, NIL, NULL); } @@ -1858,7 +2024,8 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, trigtuple, NULL)) continue; LocTriggerData.tg_trigtuple = trigtuple; @@ -1893,7 +2060,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, true, trigtuple, NULL, NIL, NULL); heap_freetuple(trigtuple); } @@ -1935,7 +2102,8 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, modifiedCols)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + modifiedCols, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; @@ -1958,7 +2126,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo) TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, false, NULL, NULL, NIL, GetModifiedColumns(relinfo, estate)); } @@ -2003,7 +2171,8 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, modifiedCols)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + modifiedCols, trigtuple, newtuple)) continue; LocTriggerData.tg_trigtuple = trigtuple; @@ -2037,7 +2206,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, true, trigtuple, newtuple, recheckIndexes, GetModifiedColumns(relinfo, estate)); heap_freetuple(trigtuple); @@ -2077,7 +2246,8 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo) Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; - if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; @@ -2100,7 +2270,7 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo) TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) - AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE, + AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_TRUNCATE, false, NULL, NULL, NIL, NULL); } @@ -2219,7 +2389,10 @@ ltrmark:; * Is trigger enabled to fire? */ static bool -TriggerEnabled(Trigger *trigger, TriggerEvent event, Bitmapset *modifiedCols) +TriggerEnabled(EState *estate, ResultRelInfo *relinfo, + Trigger *trigger, TriggerEvent event, + Bitmapset *modifiedCols, + HeapTuple oldtup, HeapTuple newtup) { /* Check replication-role-dependent enable state */ if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA) @@ -2258,6 +2431,94 @@ TriggerEnabled(Trigger *trigger, TriggerEvent event, Bitmapset *modifiedCols) return false; } + /* Check for WHEN clause */ + if (trigger->tgqual) + { + TupleDesc tupdesc = RelationGetDescr(relinfo->ri_RelationDesc); + List **predicate; + ExprContext *econtext; + TupleTableSlot *oldslot = NULL; + TupleTableSlot *newslot = NULL; + MemoryContext oldContext; + int i; + + Assert(estate != NULL); + + /* + * trigger is an element of relinfo->ri_TrigDesc->triggers[]; + * find the matching element of relinfo->ri_TrigWhenExprs[] + */ + i = trigger - relinfo->ri_TrigDesc->triggers; + predicate = &relinfo->ri_TrigWhenExprs[i]; + + /* + * If first time through for this WHEN expression, build expression + * nodetrees for it. Keep them in the per-query memory context so + * they'll survive throughout the query. + */ + if (*predicate == NIL) + { + Node *tgqual; + + oldContext = MemoryContextSwitchTo(estate->es_query_cxt); + tgqual = stringToNode(trigger->tgqual); + /* Change references to OLD and NEW to INNER and OUTER */ + ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER, 0); + ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER, 0); + /* ExecQual wants implicit-AND form */ + tgqual = (Node *) make_ands_implicit((Expr *) tgqual); + *predicate = (List *) ExecPrepareExpr((Expr *) tgqual, estate); + MemoryContextSwitchTo(oldContext); + } + + /* + * We will use the EState's per-tuple context for evaluating WHEN + * expressions (creating it if it's not already there). + */ + econtext = GetPerTupleExprContext(estate); + + /* + * Put OLD and NEW tuples into tupleslots for expression evaluation. + * These slots can be shared across the whole estate, but be careful + * that they have the current resultrel's tupdesc. + */ + if (HeapTupleIsValid(oldtup)) + { + if (estate->es_trig_oldtup_slot == NULL) + { + oldContext = MemoryContextSwitchTo(estate->es_query_cxt); + estate->es_trig_oldtup_slot = ExecInitExtraTupleSlot(estate); + MemoryContextSwitchTo(oldContext); + } + oldslot = estate->es_trig_oldtup_slot; + if (oldslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(oldslot, tupdesc); + ExecStoreTuple(oldtup, oldslot, InvalidBuffer, false); + } + if (HeapTupleIsValid(newtup)) + { + if (estate->es_trig_tuple_slot == NULL) + { + oldContext = MemoryContextSwitchTo(estate->es_query_cxt); + estate->es_trig_tuple_slot = ExecInitExtraTupleSlot(estate); + MemoryContextSwitchTo(oldContext); + } + newslot = estate->es_trig_tuple_slot; + if (newslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(newslot, tupdesc); + ExecStoreTuple(newtup, newslot, InvalidBuffer, false); + } + + /* + * Finally evaluate the expression, making the old and/or new tuples + * available as INNER/OUTER respectively. + */ + econtext->ecxt_innertuple = oldslot; + econtext->ecxt_outertuple = newslot; + if (!ExecQual(*predicate, econtext, false)) + return false; + } + return true; } @@ -3883,7 +4144,8 @@ AfterTriggerPendingOnRel(Oid relid) * ---------- */ static void -AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, +AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, + int event, bool row_trigger, HeapTuple oldtup, HeapTuple newtup, List *recheckIndexes, Bitmapset *modifiedCols) { @@ -3993,7 +4255,8 @@ AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; - if (!TriggerEnabled(trigger, event, modifiedCols)) + if (!TriggerEnabled(estate, relinfo, trigger, event, + modifiedCols, oldtup, newtup)) continue; /* diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index d7d99bc0ae..60ded334c6 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -26,7 +26,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.334 2009/10/26 02:26:29 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.335 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -752,6 +752,7 @@ InitPlan(QueryDesc *queryDesc, int eflags) */ estate->es_tupleTable = NIL; estate->es_trig_tuple_slot = NULL; + estate->es_trig_oldtup_slot = NULL; /* mark EvalPlanQual not active */ estate->es_epqTuple = NULL; @@ -911,6 +912,8 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_TrigFunctions = (FmgrInfo *) palloc0(n * sizeof(FmgrInfo)); + resultRelInfo->ri_TrigWhenExprs = (List **) + palloc0(n * sizeof(List *)); if (doInstrument) resultRelInfo->ri_TrigInstrument = InstrAlloc(n); else @@ -919,6 +922,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, else { resultRelInfo->ri_TrigFunctions = NULL; + resultRelInfo->ri_TrigWhenExprs = NULL; resultRelInfo->ri_TrigInstrument = NULL; } resultRelInfo->ri_ConstraintExprs = NULL; diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index 226e15546f..36b72abcce 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.254 2009/11/04 22:26:05 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.255 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -491,26 +491,15 @@ ExecEvalVar(ExprState *exprstate, ExprContext *econtext, if (isDone) *isDone = ExprSingleResult; - /* - * Get the input slot and attribute number we want - * - * The asserts check that references to system attributes only appear at - * the level of a relation scan; at higher levels, system attributes must - * be treated as ordinary variables (since we no longer have access to the - * original tuple). - */ - attnum = variable->varattno; - + /* Get the input slot and attribute number we want */ switch (variable->varno) { case INNER: /* get the tuple from the inner node */ slot = econtext->ecxt_innertuple; - Assert(attnum > 0); break; case OUTER: /* get the tuple from the outer node */ slot = econtext->ecxt_outertuple; - Assert(attnum > 0); break; default: /* get the tuple from the relation being @@ -519,6 +508,8 @@ ExecEvalVar(ExprState *exprstate, ExprContext *econtext, break; } + attnum = variable->varattno; + if (attnum != InvalidAttrNumber) { /* @@ -715,7 +706,7 @@ ExecEvalWholeRowVar(ExprState *exprstate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone) { Var *variable = (Var *) exprstate->expr; - TupleTableSlot *slot = econtext->ecxt_scantuple; + TupleTableSlot *slot; HeapTuple tuple; TupleDesc tupleDesc; HeapTupleHeader dtuple; @@ -724,6 +715,23 @@ ExecEvalWholeRowVar(ExprState *exprstate, ExprContext *econtext, *isDone = ExprSingleResult; *isNull = false; + /* Get the input slot we want */ + switch (variable->varno) + { + case INNER: /* get the tuple from the inner node */ + slot = econtext->ecxt_innertuple; + break; + + case OUTER: /* get the tuple from the outer node */ + slot = econtext->ecxt_outertuple; + break; + + default: /* get the tuple from the relation being + * scanned */ + slot = econtext->ecxt_scantuple; + break; + } + tuple = ExecFetchSlotTuple(slot); tupleDesc = slot->tts_tupleDescriptor; @@ -766,7 +774,7 @@ ExecEvalWholeRowSlow(ExprState *exprstate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone) { Var *variable = (Var *) exprstate->expr; - TupleTableSlot *slot = econtext->ecxt_scantuple; + TupleTableSlot *slot; HeapTuple tuple; TupleDesc var_tupdesc; HeapTupleHeader dtuple; @@ -775,6 +783,23 @@ ExecEvalWholeRowSlow(ExprState *exprstate, ExprContext *econtext, *isDone = ExprSingleResult; *isNull = false; + /* Get the input slot we want */ + switch (variable->varno) + { + case INNER: /* get the tuple from the inner node */ + slot = econtext->ecxt_innertuple; + break; + + case OUTER: /* get the tuple from the outer node */ + slot = econtext->ecxt_outertuple; + break; + + default: /* get the tuple from the relation being + * scanned */ + slot = econtext->ecxt_scantuple; + break; + } + /* * Currently, the only case handled here is stripping of trailing resjunk * fields, which we do in a slightly chintzy way by just adjusting the diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index d3352f1f5d..e1aa8ce8ac 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/execUtils.c,v 1.165 2009/10/26 02:26:29 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/executor/execUtils.c,v 1.166 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -117,6 +117,7 @@ CreateExecutorState(void) estate->es_trig_target_relations = NIL; estate->es_trig_tuple_slot = NULL; + estate->es_trig_oldtup_slot = NULL; estate->es_param_list_info = NULL; estate->es_param_exec_vals = NULL; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 3f1f9c093e..33b87cb63c 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/nodeModifyTable.c,v 1.2 2009/10/26 02:26:31 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/executor/nodeModifyTable.c,v 1.3 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -215,9 +215,10 @@ ExecInsert(TupleTableSlot *slot, * slot should not try to clear it. */ TupleTableSlot *newslot = estate->es_trig_tuple_slot; + TupleDesc tupdesc = RelationGetDescr(resultRelationDesc); - if (newslot->tts_tupleDescriptor != slot->tts_tupleDescriptor) - ExecSetSlotDescriptor(newslot, slot->tts_tupleDescriptor); + if (newslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(newslot, tupdesc); ExecStoreTuple(newtuple, newslot, InvalidBuffer, false); slot = newslot; tuple = newtuple; @@ -467,9 +468,10 @@ ExecUpdate(ItemPointer tupleid, * slot should not try to clear it. */ TupleTableSlot *newslot = estate->es_trig_tuple_slot; + TupleDesc tupdesc = RelationGetDescr(resultRelationDesc); - if (newslot->tts_tupleDescriptor != slot->tts_tupleDescriptor) - ExecSetSlotDescriptor(newslot, slot->tts_tupleDescriptor); + if (newslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(newslot, tupdesc); ExecStoreTuple(newtuple, newslot, InvalidBuffer, false); slot = newslot; tuple = newtuple; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index b48909a3c5..35656d8656 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.451 2009/11/16 21:32:06 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.452 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -3180,6 +3180,7 @@ _copyCreateTrigStmt(CreateTrigStmt *from) COPY_SCALAR_FIELD(row); COPY_SCALAR_FIELD(events); COPY_NODE_FIELD(columns); + COPY_NODE_FIELD(whenClause); COPY_SCALAR_FIELD(isconstraint); COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index c69468ae7a..eeda5799ee 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -22,7 +22,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.373 2009/11/16 21:32:06 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.374 2009/11/20 20:38:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1670,6 +1670,7 @@ _equalCreateTrigStmt(CreateTrigStmt *a, CreateTrigStmt *b) COMPARE_SCALAR_FIELD(row); COMPARE_SCALAR_FIELD(events); COMPARE_NODE_FIELD(columns); + COMPARE_NODE_FIELD(whenClause); COMPARE_SCALAR_FIELD(isconstraint); COMPARE_SCALAR_FIELD(deferrable); COMPARE_SCALAR_FIELD(initdeferred); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e80fffd3be..cab6d915c8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.693 2009/11/16 21:32:06 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.694 2009/11/20 20:38:10 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -249,6 +249,7 @@ static TypeName *TableFuncTypeName(List *columns); %type TriggerEvents TriggerOneEvent %type TriggerFuncArg +%type TriggerWhen %type copy_file_name database_name access_method_clause access_method attr_name @@ -3227,18 +3228,19 @@ AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generi CreateTrigStmt: CREATE TRIGGER name TriggerActionTime TriggerEvents ON - qualified_name TriggerForSpec EXECUTE PROCEDURE - func_name '(' TriggerFuncArgs ')' + qualified_name TriggerForSpec TriggerWhen + EXECUTE PROCEDURE func_name '(' TriggerFuncArgs ')' { CreateTrigStmt *n = makeNode(CreateTrigStmt); n->trigname = $3; n->relation = $7; - n->funcname = $11; - n->args = $13; + n->funcname = $12; + n->args = $14; n->before = $4; n->row = $8; n->events = intVal(linitial($5)); n->columns = (List *) lsecond($5); + n->whenClause = $9; n->isconstraint = FALSE; n->deferrable = FALSE; n->initdeferred = FALSE; @@ -3246,20 +3248,20 @@ CreateTrigStmt: $$ = (Node *)n; } | CREATE CONSTRAINT TRIGGER name AFTER TriggerEvents ON - qualified_name OptConstrFromTable - ConstraintAttributeSpec - FOR EACH ROW EXECUTE PROCEDURE - func_name '(' TriggerFuncArgs ')' + qualified_name OptConstrFromTable ConstraintAttributeSpec + FOR EACH ROW TriggerWhen + EXECUTE PROCEDURE func_name '(' TriggerFuncArgs ')' { CreateTrigStmt *n = makeNode(CreateTrigStmt); n->trigname = $4; n->relation = $8; - n->funcname = $16; - n->args = $18; + n->funcname = $17; + n->args = $19; n->before = FALSE; n->row = TRUE; n->events = intVal(linitial($6)); n->columns = (List *) lsecond($6); + n->whenClause = $14; n->isconstraint = TRUE; n->deferrable = ($10 & 1) != 0; n->initdeferred = ($10 & 2) != 0; @@ -3335,6 +3337,11 @@ TriggerForType: | STATEMENT { $$ = FALSE; } ; +TriggerWhen: + WHEN '(' a_expr ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + TriggerFuncArgs: TriggerFuncArg { $$ = list_make1($1); } | TriggerFuncArgs ',' TriggerFuncArg { $$ = lappend($1, $3); } diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index f0ef9d6406..42071bfb6c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.317 2009/11/16 21:32:07 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.318 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -939,7 +939,7 @@ ProcessUtility(Node *parsetree, break; case T_CreateTrigStmt: - CreateTrigger((CreateTrigStmt *) parsetree, + CreateTrigger((CreateTrigStmt *) parsetree, queryString, InvalidOid, InvalidOid, NULL, true); break; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 3c49d37a2b..fc5885e68e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.314 2009/11/05 23:24:25 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.315 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -487,6 +487,8 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) SysScanDesc tgscan; int findx = 0; char *tgname; + Datum value; + bool isnull; /* * Fetch the pg_trigger tuple by the Oid of the trigger @@ -543,6 +545,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) appendStringInfo(&buf, " OR UPDATE"); else appendStringInfo(&buf, " UPDATE"); + findx++; /* tgattr is first var-width field, so OK to access directly */ if (trigrec->tgattr.dim1 > 0) { @@ -567,6 +570,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) appendStringInfo(&buf, " OR TRUNCATE"); else appendStringInfo(&buf, " TRUNCATE"); + findx++; } appendStringInfo(&buf, " ON %s", generate_relation_name(trigrec->tgrelid, NIL)); @@ -574,7 +578,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) if (trigrec->tgisconstraint) { - if (trigrec->tgconstrrelid != InvalidOid) + if (OidIsValid(trigrec->tgconstrrelid)) { appendStringInfo(&buf, "FROM %s", generate_relation_name(trigrec->tgconstrrelid, NIL)); @@ -596,23 +600,70 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) appendStringInfo(&buf, "FOR EACH STATEMENT"); appendStringInfoString(&buf, pretty ? "\n " : " "); + /* If the trigger has a WHEN qualification, add that */ + value = fastgetattr(ht_trig, Anum_pg_trigger_tgqual, + tgrel->rd_att, &isnull); + if (!isnull) + { + Node *qual; + deparse_context context; + deparse_namespace dpns; + RangeTblEntry *oldrte; + RangeTblEntry *newrte; + + appendStringInfoString(&buf, "WHEN ("); + + qual = stringToNode(TextDatumGetCString(value)); + + /* Build minimal OLD and NEW RTEs for the rel */ + oldrte = makeNode(RangeTblEntry); + oldrte->rtekind = RTE_RELATION; + oldrte->relid = trigrec->tgrelid; + oldrte->eref = makeAlias("old", NIL); + oldrte->inh = false; + oldrte->inFromCl = true; + + newrte = makeNode(RangeTblEntry); + newrte->rtekind = RTE_RELATION; + newrte->relid = trigrec->tgrelid; + newrte->eref = makeAlias("new", NIL); + newrte->inh = false; + newrte->inFromCl = true; + + /* Build two-element rtable */ + dpns.rtable = list_make2(oldrte, newrte); + dpns.ctes = NIL; + dpns.subplans = NIL; + dpns.outer_plan = dpns.inner_plan = NULL; + + /* Set up context with one-deep namespace stack */ + context.buf = &buf; + context.namespaces = list_make1(&dpns); + context.windowClause = NIL; + context.windowTList = NIL; + context.varprefix = true; + context.prettyFlags = pretty ? PRETTYFLAG_PAREN | PRETTYFLAG_INDENT : 0; + context.indentLevel = PRETTYINDENT_STD; + + get_rule_expr(qual, &context, false); + + appendStringInfo(&buf, ")%s", pretty ? "\n " : " "); + } + appendStringInfo(&buf, "EXECUTE PROCEDURE %s(", generate_function_name(trigrec->tgfoid, 0, NIL, NULL, NULL)); if (trigrec->tgnargs > 0) { - bytea *val; - bool isnull; char *p; int i; - val = DatumGetByteaP(fastgetattr(ht_trig, - Anum_pg_trigger_tgargs, - tgrel->rd_att, &isnull)); + value = fastgetattr(ht_trig, Anum_pg_trigger_tgargs, + tgrel->rd_att, &isnull); if (isnull) elog(ERROR, "tgargs is null for trigger %u", trigid); - p = (char *) VARDATA(val); + p = (char *) VARDATA(DatumGetByteaP(value)); for (i = 0; i < trigrec->tgnargs; i++) { if (i > 0) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index e25bf7ab0b..5b3b36757d 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -12,7 +12,7 @@ * by PostgreSQL * * IDENTIFICATION - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.552 2009/10/14 22:14:23 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.553 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -4305,10 +4305,15 @@ getTriggers(TableInfo tblinfo[], int numTables) resetPQExpBuffer(query); if (g_fout->remoteVersion >= 80500) { + /* + * NB: think not to use pretty=true in pg_get_triggerdef. It + * could result in non-forward-compatible dumps of WHEN clauses + * due to under-parenthesization. + */ appendPQExpBuffer(query, "SELECT tgname, " "tgfoid::pg_catalog.regproc AS tgfname, " - "pg_catalog.pg_get_triggerdef(oid, true) AS tgdef, " + "pg_catalog.pg_get_triggerdef(oid, false) AS tgdef, " "tgenabled, tableoid, oid " "FROM pg_catalog.pg_trigger t " "WHERE tgrelid = '%u'::pg_catalog.oid " @@ -11323,6 +11328,7 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo) appendPQExpBuffer(query, " OR UPDATE"); else appendPQExpBuffer(query, " UPDATE"); + findx++; } if (TRIGGER_FOR_TRUNCATE(tginfo->tgtype)) { @@ -11330,6 +11336,7 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo) appendPQExpBuffer(query, " OR TRUNCATE"); else appendPQExpBuffer(query, " TRUNCATE"); + findx++; } appendPQExpBuffer(query, " ON %s\n", fmtId(tbinfo->dobj.name)); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 88e14360d7..4be0e0fce6 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.550 2009/11/05 23:24:26 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.551 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200911051 +#define CATALOG_VERSION_NO 200911201 #endif diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.h index eb6a583fd2..11b8480339 100644 --- a/src/include/catalog/pg_trigger.h +++ b/src/include/catalog/pg_trigger.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_trigger.h,v 1.35 2009/10/14 22:14:24 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_trigger.h,v 1.36 2009/11/20 20:38:11 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -52,9 +52,10 @@ CATALOG(pg_trigger,2620) bool tginitdeferred; /* constraint trigger is deferred initially */ int2 tgnargs; /* # of extra arguments in tgargs */ - /* VARIABLE LENGTH FIELDS (note: these are not supposed to be null) */ + /* VARIABLE LENGTH FIELDS (note: tgattr and tgargs must not be null) */ int2vector tgattr; /* column numbers, if trigger is on columns */ bytea tgargs; /* first\000second\000tgnargs\000 */ + text tgqual; /* WHEN expression, or NULL if none */ } FormData_pg_trigger; /* ---------------- @@ -68,7 +69,7 @@ typedef FormData_pg_trigger *Form_pg_trigger; * compiler constants for pg_trigger * ---------------- */ -#define Natts_pg_trigger 15 +#define Natts_pg_trigger 16 #define Anum_pg_trigger_tgrelid 1 #define Anum_pg_trigger_tgname 2 #define Anum_pg_trigger_tgfoid 3 @@ -84,6 +85,7 @@ typedef FormData_pg_trigger *Form_pg_trigger; #define Anum_pg_trigger_tgnargs 13 #define Anum_pg_trigger_tgattr 14 #define Anum_pg_trigger_tgargs 15 +#define Anum_pg_trigger_tgqual 16 /* Bits within tgtype */ #define TRIGGER_TYPE_ROW (1 << 0) diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.h index fb83aa75df..e594237e2b 100644 --- a/src/include/catalog/toasting.h +++ b/src/include/catalog/toasting.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/toasting.h,v 1.9 2009/10/07 22:14:25 alvherre Exp $ + * $PostgreSQL: pgsql/src/include/catalog/toasting.h,v 1.10 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -47,6 +47,7 @@ DECLARE_TOAST(pg_description, 2834, 2835); DECLARE_TOAST(pg_proc, 2836, 2837); DECLARE_TOAST(pg_rewrite, 2838, 2839); DECLARE_TOAST(pg_statistic, 2840, 2841); +DECLARE_TOAST(pg_trigger, 2336, 2337); /* shared catalogs */ DECLARE_TOAST(pg_authid, 2842, 2843); diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 2bf40b76af..c461e70635 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/trigger.h,v 1.77 2009/10/26 02:26:41 tgl Exp $ + * $PostgreSQL: pgsql/src/include/commands/trigger.h,v 1.78 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -104,7 +104,7 @@ extern PGDLLIMPORT int SessionReplicationRole; #define TRIGGER_FIRES_ON_REPLICA 'R' #define TRIGGER_DISABLED 'D' -extern Oid CreateTrigger(CreateTrigStmt *stmt, +extern Oid CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index bba9c0370b..3bff0b5a6d 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.211 2009/10/26 02:26:41 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.212 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -294,6 +294,7 @@ typedef struct JunkFilter * IndexRelationInfo array of key/attr info for indices * TrigDesc triggers to be fired, if any * TrigFunctions cached lookup info for trigger functions + * TrigWhenExprs array of trigger WHEN expr states * TrigInstrument optional runtime measurements for triggers * ConstraintExprs array of constraint-checking expr states * junkFilter for removing junk attributes from tuples @@ -310,6 +311,7 @@ typedef struct ResultRelInfo IndexInfo **ri_IndexRelationInfo; TriggerDesc *ri_TrigDesc; FmgrInfo *ri_TrigFunctions; + List **ri_TrigWhenExprs; struct Instrumentation *ri_TrigInstrument; List **ri_ConstraintExprs; JunkFilter *ri_junkFilter; @@ -345,7 +347,8 @@ typedef struct EState /* Stuff used for firing triggers: */ List *es_trig_target_relations; /* trigger-only ResultRelInfos */ - TupleTableSlot *es_trig_tuple_slot; /* for trigger output tuples */ + TupleTableSlot *es_trig_tuple_slot; /* for trigger output tuples */ + TupleTableSlot *es_trig_oldtup_slot; /* for trigger old tuples */ /* Parameter info: */ ParamListInfo es_param_list_info; /* values of external params */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b51f1d9892..ad95ac9bb4 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.415 2009/11/16 21:32:07 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.416 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1571,6 +1571,7 @@ typedef struct CreateTrigStmt /* events uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */ int16 events; /* INSERT/UPDATE/DELETE/TRUNCATE */ List *columns; /* column names, or NIL for all columns */ + Node *whenClause; /* qual expression, or NULL if none */ /* The following are used for constraint triggers (RI and unique checks) */ bool isconstraint; /* This is a constraint trigger */ diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 37164f3b5d..0ef6642f98 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/rel.h,v 1.115 2009/07/28 02:56:31 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/rel.h,v 1.116 2009/11/20 20:38:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -66,6 +66,7 @@ typedef struct Trigger int16 tgnattr; int16 *tgattr; char **tgargs; + char *tgqual; } Trigger; typedef struct TriggerDesc diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index b21c93f3b5..aa74083380 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -322,6 +322,90 @@ SELECT * FROM main_table ORDER BY a, b; | (8 rows) +-- +-- test triggers with WHEN clause +-- +CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a'); +CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table +FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any'); +CREATE TRIGGER insert_a AFTER INSERT ON main_table +FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a'); +CREATE TRIGGER delete_a AFTER DELETE ON main_table +FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a'); +CREATE TRIGGER insert_when BEFORE INSERT ON main_table +FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when'); +CREATE TRIGGER delete_when AFTER DELETE ON main_table +FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when'); +INSERT INTO main_table (a) VALUES (123), (456); +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +COPY main_table FROM stdin; +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +DELETE FROM main_table WHERE a IN (123, 456); +NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW +NOTICE: trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW +NOTICE: trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT +UPDATE main_table SET a = 50, b = 60; +NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW +NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +SELECT * 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) + +SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; + pg_get_triggerdef +-------------------------------------------------- + CREATE TRIGGER modified_a + BEFORE UPDATE OF a ON main_table + FOR EACH ROW + WHEN (old.a <> new.a) + EXECUTE PROCEDURE trigger_func('modified_a') +(1 row) + +SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; + pg_get_triggerdef +---------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE PROCEDURE trigger_func('modified_a') +(1 row) + +SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; + pg_get_triggerdef +---------------------------------------------------- + CREATE TRIGGER modified_any + BEFORE UPDATE OF a ON main_table + FOR EACH ROW + WHEN (old.* IS DISTINCT FROM new.*) + EXECUTE PROCEDURE trigger_func('modified_any') +(1 row) + +DROP TRIGGER modified_a ON main_table; +DROP TRIGGER modified_any ON main_table; +DROP TRIGGER insert_a ON main_table; +DROP TRIGGER delete_a ON main_table; +DROP TRIGGER insert_when ON main_table; +DROP TRIGGER delete_when ON main_table; -- 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 @@ -393,6 +477,30 @@ FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a'); ERROR: syntax error at or near "OF" LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table ^ +CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) +EXECUTE PROCEDURE trigger_func('error_ins_old'); +ERROR: INSERT trigger's WHEN condition cannot reference OLD values +LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) + ^ +CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) +EXECUTE PROCEDURE trigger_func('error_del_new'); +ERROR: DELETE trigger's WHEN condition cannot reference NEW values +LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) + ^ +CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table +FOR EACH ROW WHEN (NEW.tableoid <> 0) +EXECUTE PROCEDURE trigger_func('error_when_sys_column'); +ERROR: BEFORE trigger's WHEN condition cannot reference NEW system columns +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 +LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) + ^ -- check dependency restrictions ALTER TABLE main_table DROP COLUMN b; ERROR: cannot drop table main_table column b because other objects depend on it diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 70c563477c..6940689c23 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -254,6 +254,40 @@ COPY main_table (a, b) FROM stdin; SELECT * FROM main_table ORDER BY a, b; +-- +-- test triggers with WHEN clause +-- + +CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a'); +CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table +FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any'); +CREATE TRIGGER insert_a AFTER INSERT ON main_table +FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a'); +CREATE TRIGGER delete_a AFTER DELETE ON main_table +FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a'); +CREATE TRIGGER insert_when BEFORE INSERT ON main_table +FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when'); +CREATE TRIGGER delete_when AFTER DELETE ON main_table +FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when'); +INSERT INTO main_table (a) VALUES (123), (456); +COPY main_table FROM stdin; +123 999 +456 999 +\. +DELETE FROM main_table WHERE a IN (123, 456); +UPDATE main_table SET a = 50, b = 60; +SELECT * FROM main_table ORDER BY a, b; +SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; +SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a'; +SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any'; +DROP TRIGGER modified_a ON main_table; +DROP TRIGGER modified_any ON main_table; +DROP TRIGGER insert_a ON main_table; +DROP TRIGGER delete_a ON main_table; +DROP TRIGGER insert_when ON main_table; +DROP TRIGGER delete_when ON main_table; + -- Test column-level triggers DROP TRIGGER after_upd_row_trig ON main_table; @@ -282,6 +316,18 @@ CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a'); CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a'); +CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) +EXECUTE PROCEDURE trigger_func('error_ins_old'); +CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table +FOR EACH ROW WHEN (OLD.a <> NEW.a) +EXECUTE PROCEDURE trigger_func('error_del_new'); +CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table +FOR EACH ROW WHEN (NEW.tableoid <> 0) +EXECUTE PROCEDURE trigger_func('error_when_sys_column'); +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'); -- check dependency restrictions ALTER TABLE main_table DROP COLUMN b; -- 2.40.0