diff -cprN head/doc/src/sgml/ref/create_trigger.sgml work/doc/src/sgml/ref/create_trigger.sgml *** head/doc/src/sgml/ref/create_trigger.sgml 2009-10-15 07:14:21.000000000 +0900 --- work/doc/src/sgml/ref/create_trigger.sgml 2009-11-13 12:04:10.402827933 +0900 *************** PostgreSQL documentation *** 23,28 **** --- 23,29 ---- CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] + WHEN ( condition ) EXECUTE PROCEDURE function_name ( arguments ) *************** UPDATE OF column_name1 + condition + + + Any SQL conditional expression (returning + boolean). Only FOR EACH ROW triggers + can refer NEW and OLD tuples. + INSERT trigger can refer NEW, + DELETE trigger can refer OLD, + and UPDATE trigger can refer both of them. + + + + + function_name diff -cprN head/src/backend/catalog/index.c work/src/backend/catalog/index.c *** head/src/backend/catalog/index.c 2009-10-15 07:14:21.000000000 +0900 --- work/src/backend/catalog/index.c 2009-11-13 12:04:10.403865029 +0900 *************** index_create(Oid heapRelationId, *** 798,804 **** trigger->initdeferred = initdeferred; trigger->constrrel = NULL; ! (void) CreateTrigger(trigger, conOid, indexRelationId, isprimary ? "PK_ConstraintTrigger" : "Unique_ConstraintTrigger", false); --- 798,804 ---- trigger->initdeferred = initdeferred; trigger->constrrel = NULL; ! (void) CreateTrigger(trigger, NULL, conOid, indexRelationId, isprimary ? "PK_ConstraintTrigger" : "Unique_ConstraintTrigger", false); diff -cprN head/src/backend/commands/tablecmds.c work/src/backend/commands/tablecmds.c *** head/src/backend/commands/tablecmds.c 2009-11-04 21:24:23.000000000 +0900 --- work/src/backend/commands/tablecmds.c 2009-11-13 12:04:10.405853692 +0900 *************** CreateFKCheckTrigger(RangeVar *myRel, Co *** 5482,5488 **** fk_trigger->constrrel = fkconstraint->pktable; fk_trigger->args = NIL; ! (void) CreateTrigger(fk_trigger, constraintOid, indexOid, "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ --- 5482,5488 ---- fk_trigger->constrrel = fkconstraint->pktable; fk_trigger->args = NIL; ! (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ *************** createForeignKeyTriggers(Relation rel, C *** 5523,5528 **** --- 5523,5529 ---- fk_trigger = makeNode(CreateTrigStmt); fk_trigger->trigname = fkconstraint->conname; fk_trigger->relation = fkconstraint->pktable; + fk_trigger->whenClause = NULL; fk_trigger->before = false; fk_trigger->row = true; fk_trigger->events = TRIGGER_TYPE_DELETE; *************** createForeignKeyTriggers(Relation rel, C *** 5563,5569 **** } fk_trigger->args = NIL; ! (void) CreateTrigger(fk_trigger, constraintOid, indexOid, "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ --- 5564,5570 ---- } fk_trigger->args = NIL; ! (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ *************** createForeignKeyTriggers(Relation rel, C *** 5576,5581 **** --- 5577,5583 ---- fk_trigger = makeNode(CreateTrigStmt); fk_trigger->trigname = fkconstraint->conname; fk_trigger->relation = fkconstraint->pktable; + fk_trigger->whenClause = NULL; fk_trigger->before = false; fk_trigger->row = true; fk_trigger->events = TRIGGER_TYPE_UPDATE; *************** createForeignKeyTriggers(Relation rel, C *** 5616,5622 **** } fk_trigger->args = NIL; ! (void) CreateTrigger(fk_trigger, constraintOid, indexOid, "RI_ConstraintTrigger", false); } --- 5618,5624 ---- } fk_trigger->args = NIL; ! (void) CreateTrigger(fk_trigger, NULL, constraintOid, indexOid, "RI_ConstraintTrigger", false); } diff -cprN head/src/backend/commands/trigger.c work/src/backend/commands/trigger.c *** head/src/backend/commands/trigger.c 2009-10-28 05:14:27.000000000 +0900 --- work/src/backend/commands/trigger.c 2009-11-13 12:17:03.294907695 +0900 *************** *** 32,41 **** --- 32,43 ---- #include "miscadmin.h" #include "nodes/bitmapset.h" #include "nodes/makefuncs.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" *************** static HeapTuple GetTupleForTrigger(ESta *** 66,78 **** ItemPointer tid, TupleTableSlot **newSlot); static bool TriggerEnabled(Trigger *trigger, TriggerEvent event, ! Bitmapset *modifiedCols); 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, List *recheckIndexes, Bitmapset *modifiedCols); --- 68,81 ---- ItemPointer tid, TupleTableSlot **newSlot); static bool TriggerEnabled(Trigger *trigger, TriggerEvent event, ! Bitmapset *modifiedCols, EState *estate, ! TupleDesc tupdesc, HeapTuple oldtup, HeapTuple newtup); static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata, int tgindx, FmgrInfo *finfo, Instrumentation *instr, MemoryContext per_tuple_context); ! static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, int event, bool row_trigger, HeapTuple oldtup, HeapTuple newtup, List *recheckIndexes, Bitmapset *modifiedCols); *************** static void AfterTriggerSaveEvent(Result *** 101,107 **** * but a foreign-key constraint. This is a kluge for backwards compatibility. */ Oid ! CreateTrigger(CreateTrigStmt *stmt, Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions) { --- 104,110 ---- * but a foreign-key constraint. This is a kluge for backwards compatibility. */ Oid ! CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions) { *************** CreateTrigger(CreateTrigStmt *stmt, *** 128,133 **** --- 131,137 ---- Oid constrrelid = InvalidOid; ObjectAddress myself, referenced; + char *qual; rel = heap_openrv(stmt->relation, AccessExclusiveLock); *************** CreateTrigger(CreateTrigStmt *stmt, *** 224,229 **** --- 228,295 ---- return InvalidOid; } + if (stmt->whenClause == NULL) + qual = NULL; + else + { + ParseState *pstate; + Node *whenClause; + int num_rte; + + /* Set up pstate */ + pstate = make_parsestate(NULL); + pstate->p_sourcetext = queryString; + + /* + * NOTE: 'OLD' must always have a varno equal to 1 and 'NEW' equal to 2. + * Set up their RTEs in the main pstate for use in parsing the rule + * qualification. + */ + if (TRIGGER_FOR_ROW(tgtype)) + { + RangeTblEntry *rte; + + if ((TRIGGER_FOR_DELETE(tgtype) || TRIGGER_FOR_UPDATE(tgtype)) && + !TRIGGER_FOR_INSERT(tgtype)) + { + rte = addRangeTableEntryForRelation(pstate, rel, + makeAlias("old", NIL), false, false); + rte->requiredPerms = 0; + addRTEtoQuery(pstate, rte, false, true, true); + } + + if ((TRIGGER_FOR_INSERT(tgtype) || TRIGGER_FOR_UPDATE(tgtype)) && + !TRIGGER_FOR_DELETE(tgtype)) + { + rte = addRangeTableEntryForRelation(pstate, rel, + makeAlias("new", NIL), false, false); + rte->requiredPerms = 0; + addRTEtoQuery(pstate, rte, false, true, true); + } + } + num_rte = list_length(pstate->p_rtable); + + /* take care of the when clause */ + whenClause = transformWhereClause(pstate, + (Node *) copyObject(stmt->whenClause), "WHEN"); + + if (list_length(pstate->p_rtable) != num_rte) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("trigger WHEN condition cannot contain references to other relations"))); + + /* + * adjust varno of NEW reference to PRS2_NEW_VARNO. + * XXX: Are there any better ways? + */ + if (num_rte == 1 && TRIGGER_FOR_INSERT(tgtype)) + ChangeVarNodes(whenClause, 1, PRS2_NEW_VARNO, 0); + + free_parsestate(pstate); + + qual = nodeToString(whenClause); + } + /* * Generate the trigger's OID now, so that we can use it in the name if * needed. *************** CreateTrigger(CreateTrigStmt *stmt, *** 387,392 **** --- 453,469 ---- 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 + { + values[Anum_pg_trigger_tgqual - 1] = InvalidOid; + nulls[Anum_pg_trigger_tgqual - 1] = true; + } + tuple = heap_form_tuple(tgrel->rd_att, values, nulls); /* force tuple to have the desired OID */ *************** RelationBuildTriggers(Relation relation) *** 1184,1189 **** --- 1261,1268 ---- { Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup); Trigger *build; + Datum qual_datum; + bool isnull; if (numtrigs >= maxtrigs) { *************** RelationBuildTriggers(Relation relation) *** 1238,1243 **** --- 1317,1338 ---- else build->tgargs = NULL; + qual_datum = heap_getattr(htup, Anum_pg_trigger_tgqual, + RelationGetDescr(tgrel), &isnull); + if (!isnull) + { + char *qual_str = TextDatumGetCString(qual_datum); + + /* Fix references to OLD and NEW to INNER and OUTER */ + build->tgqual = stringToNode(qual_str); + ChangeVarNodes(build->tgqual, PRS2_OLD_VARNO, INNER, 0); + ChangeVarNodes(build->tgqual, PRS2_NEW_VARNO, OUTER, 0); + + pfree(qual_str); + } + else + build->tgqual = NULL; + numtrigs++; } *************** CopyTriggerDesc(TriggerDesc *trigdesc) *** 1396,1401 **** --- 1491,1497 ---- newargs[j] = pstrdup(trigger->tgargs[j]); trigger->tgargs = newargs; } + trigger->tgqual = copyObject(trigger->tgqual); trigger++; } *************** ExecBSInsertTriggers(EState *estate, Res *** 1687,1693 **** Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) continue; LocTriggerData.tg_trigger = trigger; --- 1783,1790 ---- Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL, ! NULL, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; *************** ExecASInsertTriggers(EState *estate, Res *** 1710,1716 **** TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, false, NULL, NULL, NIL, NULL); } --- 1807,1813 ---- TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, false, NULL, NULL, NIL, NULL); } *************** ExecBRInsertTriggers(EState *estate, Res *** 1737,1743 **** { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) continue; LocTriggerData.tg_trigtuple = oldtuple = newtuple; --- 1834,1842 ---- { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL, ! estate, RelationGetDescr(relinfo->ri_RelationDesc), ! NULL, newtuple)) continue; LocTriggerData.tg_trigtuple = oldtuple = newtuple; *************** ExecARInsertTriggers(EState *estate, Res *** 1763,1769 **** TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, true, NULL, trigtuple, recheckIndexes, NULL); } --- 1862,1868 ---- TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, true, NULL, trigtuple, recheckIndexes, NULL); } *************** ExecBSDeleteTriggers(EState *estate, Res *** 1800,1806 **** Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) continue; LocTriggerData.tg_trigger = trigger; --- 1899,1906 ---- Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL, ! NULL, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; *************** ExecASDeleteTriggers(EState *estate, Res *** 1823,1829 **** TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, false, NULL, NULL, NIL, NULL); } --- 1923,1929 ---- TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, false, NULL, NULL, NIL, NULL); } *************** ExecBRDeleteTriggers(EState *estate, EPQ *** 1858,1864 **** { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) continue; LocTriggerData.tg_trigtuple = trigtuple; --- 1958,1966 ---- { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL, ! estate, RelationGetDescr(relinfo->ri_RelationDesc), ! trigtuple, NULL)) continue; LocTriggerData.tg_trigtuple = trigtuple; *************** ExecARDeleteTriggers(EState *estate, Res *** 1893,1899 **** HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, true, trigtuple, NULL, NIL, NULL); heap_freetuple(trigtuple); } --- 1995,2001 ---- HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, true, trigtuple, NULL, NIL, NULL); heap_freetuple(trigtuple); } *************** ExecBSUpdateTriggers(EState *estate, Res *** 1935,1941 **** Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, modifiedCols)) continue; LocTriggerData.tg_trigger = trigger; --- 2037,2044 ---- Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, modifiedCols, ! NULL, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; *************** ExecASUpdateTriggers(EState *estate, Res *** 1958,1964 **** TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, false, NULL, NULL, NIL, GetModifiedColumns(relinfo, estate)); } --- 2061,2067 ---- TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, false, NULL, NULL, NIL, GetModifiedColumns(relinfo, estate)); } *************** ExecBRUpdateTriggers(EState *estate, EPQ *** 2003,2009 **** { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, modifiedCols)) continue; LocTriggerData.tg_trigtuple = trigtuple; --- 2106,2114 ---- { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, modifiedCols, ! estate, RelationGetDescr(relinfo->ri_RelationDesc), ! trigtuple, newtuple)) continue; LocTriggerData.tg_trigtuple = trigtuple; *************** ExecARUpdateTriggers(EState *estate, Res *** 2037,2043 **** HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, true, trigtuple, newtuple, recheckIndexes, GetModifiedColumns(relinfo, estate)); heap_freetuple(trigtuple); --- 2142,2148 ---- HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, true, trigtuple, newtuple, recheckIndexes, GetModifiedColumns(relinfo, estate)); heap_freetuple(trigtuple); *************** ExecBSTruncateTriggers(EState *estate, R *** 2077,2083 **** Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) continue; LocTriggerData.tg_trigger = trigger; --- 2182,2189 ---- Trigger *trigger = &trigdesc->triggers[tgindx[i]]; HeapTuple newtuple; ! if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL, ! NULL, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; *************** ExecASTruncateTriggers(EState *estate, R *** 2100,2106 **** TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) ! AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE, false, NULL, NULL, NIL, NULL); } --- 2206,2212 ---- TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) ! AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_TRUNCATE, false, NULL, NULL, NIL, NULL); } *************** ltrmark:; *** 2219,2225 **** * Is trigger enabled to fire? */ static bool ! TriggerEnabled(Trigger *trigger, TriggerEvent event, Bitmapset *modifiedCols) { /* Check replication-role-dependent enable state */ if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA) --- 2325,2332 ---- * Is trigger enabled to fire? */ static bool ! TriggerEnabled(Trigger *trigger, TriggerEvent event, Bitmapset *modifiedCols, ! EState *estate, TupleDesc tupdesc, HeapTuple oldtup, HeapTuple newtup) { /* Check replication-role-dependent enable state */ if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA) *************** TriggerEnabled(Trigger *trigger, Trigger *** 2258,2263 **** --- 2365,2419 ---- return false; } + /* Check for WHEN clause */ + if (trigger->tgqual) + { + ExprContext *econtext; + List *predicate; + TupleTableSlot *oldslot = NULL; + TupleTableSlot *newslot = NULL; + + econtext = GetPerTupleExprContext(estate); + + predicate = list_make1(ExecPrepareExpr((Expr *) trigger->tgqual, estate)); + + /* set OLD and NEW tuples into tupleslots */ + if (TRIGGER_FIRED_FOR_ROW(event)) + { + Assert(tupdesc != NULL); + + if (TRIGGER_FIRED_BY_DELETE(event) || TRIGGER_FIRED_BY_UPDATE(event)) + { + Assert(oldtup != NULL); + + if (estate->es_trig_oldtup_slot == NULL) + estate->es_trig_oldtup_slot = ExecInitExtraTupleSlot(estate); + + oldslot = estate->es_trig_oldtup_slot; + if (oldslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(oldslot, tupdesc); + if (oldslot->tts_tuple != oldtup) + ExecStoreTuple(oldtup, oldslot, InvalidBuffer, false); + } + if (TRIGGER_FIRED_BY_INSERT(event) || TRIGGER_FIRED_BY_UPDATE(event)) + { + Assert(newtup != NULL); + Assert(estate->es_trig_tuple_slot != NULL); + + newslot = estate->es_trig_tuple_slot; + if (newslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(newslot, tupdesc); + if (newslot->tts_tuple != newtup) + ExecStoreTuple(newtup, newslot, InvalidBuffer, false); + } + } + + econtext->ecxt_innertuple = oldslot; + econtext->ecxt_outertuple = newslot; + if (!ExecQual(predicate, econtext, false)) + return false; + } + return true; } *************** AfterTriggerPendingOnRel(Oid relid) *** 3883,3889 **** * ---------- */ static void ! AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, HeapTuple oldtup, HeapTuple newtup, List *recheckIndexes, Bitmapset *modifiedCols) { --- 4039,4045 ---- * ---------- */ static void ! AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, int event, bool row_trigger, HeapTuple oldtup, HeapTuple newtup, List *recheckIndexes, Bitmapset *modifiedCols) { *************** AfterTriggerSaveEvent(ResultRelInfo *rel *** 3993,3999 **** { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, event, modifiedCols)) continue; /* --- 4149,4157 ---- { Trigger *trigger = &trigdesc->triggers[tgindx[i]]; ! if (!TriggerEnabled(trigger, event, modifiedCols, ! estate, RelationGetDescr(relinfo->ri_RelationDesc), ! oldtup, newtup)) continue; /* diff -cprN head/src/backend/executor/execMain.c work/src/backend/executor/execMain.c *** head/src/backend/executor/execMain.c 2009-10-26 11:26:29.000000000 +0900 --- work/src/backend/executor/execMain.c 2009-11-13 12:04:10.406801346 +0900 *************** InitPlan(QueryDesc *queryDesc, int eflag *** 752,757 **** --- 752,758 ---- */ estate->es_tupleTable = NIL; estate->es_trig_tuple_slot = NULL; + estate->es_trig_oldtup_slot = NULL; /* mark EvalPlanQual not active */ estate->es_epqTuple = NULL; diff -cprN head/src/backend/executor/execQual.c work/src/backend/executor/execQual.c *** head/src/backend/executor/execQual.c 2009-11-05 07:26:05.000000000 +0900 --- work/src/backend/executor/execQual.c 2009-11-13 12:04:10.407841338 +0900 *************** static Datum ExecEvalArrayCoerceExpr(Arr *** 170,175 **** --- 170,176 ---- bool *isNull, ExprDoneCond *isDone); static Datum ExecEvalCurrentOfExpr(ExprState *exprstate, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); + static TupleTableSlot *get_slot(ExprContext *econtext, Index varno); /* ---------------------------------------------------------------- *************** ExecEvalVar(ExprState *exprstate, ExprCo *** 501,523 **** */ attnum = variable->varattno; ! 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 ! * scanned */ ! slot = econtext->ecxt_scantuple; ! break; ! } if (attnum != InvalidAttrNumber) { --- 502,508 ---- */ attnum = variable->varattno; ! slot = get_slot(econtext, variable->varno); if (attnum != InvalidAttrNumber) { *************** ExecEvalScalarVar(ExprState *exprstate, *** 682,702 **** *isDone = ExprSingleResult; /* 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; ! 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; ! } attnum = variable->varattno; --- 667,673 ---- *isDone = ExprSingleResult; /* Get the input slot and attribute number we want */ ! slot = get_slot(econtext, variable->varno); attnum = variable->varattno; *************** ExecEvalWholeRowVar(ExprState *exprstate *** 715,721 **** bool *isNull, ExprDoneCond *isDone) { Var *variable = (Var *) exprstate->expr; ! TupleTableSlot *slot = econtext->ecxt_scantuple; HeapTuple tuple; TupleDesc tupleDesc; HeapTupleHeader dtuple; --- 686,692 ---- bool *isNull, ExprDoneCond *isDone) { Var *variable = (Var *) exprstate->expr; ! TupleTableSlot *slot = get_slot(econtext, variable->varno); HeapTuple tuple; TupleDesc tupleDesc; HeapTupleHeader dtuple; *************** ExecEvalWholeRowSlow(ExprState *exprstat *** 766,772 **** bool *isNull, ExprDoneCond *isDone) { Var *variable = (Var *) exprstate->expr; ! TupleTableSlot *slot = econtext->ecxt_scantuple; HeapTuple tuple; TupleDesc var_tupdesc; HeapTupleHeader dtuple; --- 737,743 ---- bool *isNull, ExprDoneCond *isDone) { Var *variable = (Var *) exprstate->expr; ! TupleTableSlot *slot = get_slot(econtext, variable->varno); HeapTuple tuple; TupleDesc var_tupdesc; HeapTupleHeader dtuple; *************** ExecProject(ProjectionInfo *projInfo, Ex *** 5179,5181 **** --- 5150,5166 ---- */ return ExecStoreVirtualTuple(slot); } + + static TupleTableSlot * + get_slot(ExprContext *econtext, Index varno) + { + switch (varno) + { + case INNER: /* get the tuple from the inner node */ + return econtext->ecxt_innertuple; + case OUTER: /* get the tuple from the outer node */ + return econtext->ecxt_outertuple; + default: /* get the tuple from the relation being scanned */ + return econtext->ecxt_scantuple; + } + } diff -cprN head/src/backend/executor/execUtils.c work/src/backend/executor/execUtils.c *** head/src/backend/executor/execUtils.c 2009-10-26 11:26:29.000000000 +0900 --- work/src/backend/executor/execUtils.c 2009-11-13 12:04:10.408874678 +0900 *************** CreateExecutorState(void) *** 117,122 **** --- 117,123 ---- 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 -cprN head/src/backend/executor/nodeModifyTable.c work/src/backend/executor/nodeModifyTable.c *** head/src/backend/executor/nodeModifyTable.c 2009-10-26 11:26:31.000000000 +0900 --- work/src/backend/executor/nodeModifyTable.c 2009-11-13 12:04:10.408874678 +0900 *************** ExecModifyTable(ModifyTableState *node) *** 752,757 **** --- 752,761 ---- estate->es_result_relation_info = NULL; return slot; } + + /* clear slot used by trigger to avoid dangling-reference to tuple */ + if (estate->es_trig_oldtup_slot) + ExecClearTuple(estate->es_trig_oldtup_slot); } /* Reset es_result_relation_info before exiting */ diff -cprN head/src/backend/nodes/copyfuncs.c work/src/backend/nodes/copyfuncs.c *** head/src/backend/nodes/copyfuncs.c 2009-10-28 23:55:38.000000000 +0900 --- work/src/backend/nodes/copyfuncs.c 2009-11-13 12:04:10.409885877 +0900 *************** _copyCreateTrigStmt(CreateTrigStmt *from *** 3183,3188 **** --- 3183,3189 ---- 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 -cprN head/src/backend/nodes/equalfuncs.c work/src/backend/nodes/equalfuncs.c *** head/src/backend/nodes/equalfuncs.c 2009-10-28 23:55:38.000000000 +0900 --- work/src/backend/nodes/equalfuncs.c 2009-11-13 12:04:10.409885877 +0900 *************** _equalCreateTrigStmt(CreateTrigStmt *a, *** 1673,1678 **** --- 1673,1679 ---- 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 -cprN head/src/backend/parser/gram.y work/src/backend/parser/gram.y *** head/src/backend/parser/gram.y 2009-11-12 05:31:26.000000000 +0900 --- work/src/backend/parser/gram.y 2009-11-13 12:10:46.729862473 +0900 *************** static TypeName *TableFuncTypeName(List *** 248,253 **** --- 248,254 ---- %type TriggerEvents TriggerOneEvent %type TriggerFuncArg + %type TriggerWhen %type copy_file_name database_name access_method_clause access_method attr_name *************** AlterUserMappingStmt: ALTER USER MAPPING *** 3226,3239 **** CreateTrigStmt: CREATE TRIGGER name TriggerActionTime TriggerEvents ON ! qualified_name TriggerForSpec EXECUTE PROCEDURE ! func_name '(' TriggerFuncArgs ')' { CreateTrigStmt *n = makeNode(CreateTrigStmt); n->trigname = $3; n->relation = $7; ! n->funcname = $11; ! n->args = $13; n->before = $4; n->row = $8; n->events = intVal(linitial($5)); --- 3227,3241 ---- CreateTrigStmt: CREATE TRIGGER name TriggerActionTime TriggerEvents ON ! qualified_name TriggerForSpec ! TriggerWhen EXECUTE PROCEDURE func_name '(' TriggerFuncArgs ')' { CreateTrigStmt *n = makeNode(CreateTrigStmt); n->trigname = $3; n->relation = $7; ! n->whenClause = $9; ! n->funcname = $12; ! n->args = $14; n->before = $4; n->row = $8; n->events = intVal(linitial($5)); *************** TriggerFuncArg: *** 3354,3359 **** --- 3356,3366 ---- | ColId { $$ = makeString($1); } ; + TriggerWhen: + WHEN '(' a_expr ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + OptConstrFromTable: FROM qualified_name { $$ = $2; } | /*EMPTY*/ { $$ = NULL; } diff -cprN head/src/backend/tcop/utility.c work/src/backend/tcop/utility.c *** head/src/backend/tcop/utility.c 2009-10-26 11:26:40.000000000 +0900 --- work/src/backend/tcop/utility.c 2009-11-13 12:04:10.412858953 +0900 *************** ProcessUtility(Node *parsetree, *** 939,945 **** break; case T_CreateTrigStmt: ! CreateTrigger((CreateTrigStmt *) parsetree, InvalidOid, InvalidOid, NULL, true); break; --- 939,945 ---- break; case T_CreateTrigStmt: ! CreateTrigger((CreateTrigStmt *) parsetree, queryString, InvalidOid, InvalidOid, NULL, true); break; diff -cprN head/src/backend/utils/adt/ruleutils.c work/src/backend/utils/adt/ruleutils.c *** head/src/backend/utils/adt/ruleutils.c 2009-11-06 08:24:25.000000000 +0900 --- work/src/backend/utils/adt/ruleutils.c 2009-11-13 12:17:03.352877598 +0900 *************** *** 42,47 **** --- 42,48 ---- #include "parser/keywords.h" #include "parser/parse_func.h" #include "parser/parse_oper.h" + #include "parser/parse_relation.h" #include "parser/parser.h" #include "parser/parsetree.h" #include "rewrite/rewriteHandler.h" *************** pg_get_triggerdef_worker(Oid trigid, boo *** 487,492 **** --- 488,495 ---- SysScanDesc tgscan; int findx = 0; char *tgname; + Datum value; + bool isnull; /* * Fetch the pg_trigger tuple by the Oid of the trigger *************** pg_get_triggerdef_worker(Oid trigid, boo *** 596,618 **** appendStringInfo(&buf, "FOR EACH STATEMENT"); appendStringInfoString(&buf, 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)); if (isnull) elog(ERROR, "tgargs is null for trigger %u", trigid); ! p = (char *) VARDATA(val); for (i = 0; i < trigrec->tgnargs; i++) { if (i > 0) --- 599,663 ---- appendStringInfo(&buf, "FOR EACH STATEMENT"); appendStringInfoString(&buf, pretty ? "\n " : " "); + /* If the trigger has an event qualification, add it */ + 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; + + appendStringInfo(&buf, "WHEN ("); + + qual = stringToNode(TextDatumGetCString(value)); + + context.buf = &buf; + context.namespaces = list_make1(&dpns); + context.windowClause = NIL; + context.windowTList = NIL; + context.varprefix = true; + context.prettyFlags = PRETTYFLAG_PAREN | (pretty ? PRETTYFLAG_INDENT : 0); + context.indentLevel = PRETTYINDENT_STD; + + /* Build a minimal NEW and OLD RTEs for the rel */ + oldrte = makeNode(RangeTblEntry); + oldrte->rtekind = RTE_RELATION; + oldrte->alias = oldrte->eref = makeAlias("old", NIL); + oldrte->relid = trigrec->tgrelid; + + newrte = makeNode(RangeTblEntry); + newrte->rtekind = RTE_RELATION; + newrte->alias = newrte->eref = makeAlias("new", NIL); + newrte->relid = trigrec->tgrelid; + + /* Build two-elements rtable */ + dpns.rtable = list_make2(oldrte, newrte); + dpns.ctes = NIL; + dpns.subplans = NIL; + dpns.outer_plan = dpns.inner_plan = NULL; + + 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) { char *p; int i; ! 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(DatumGetByteaP(value)); for (i = 0; i < trigrec->tgnargs; i++) { if (i > 0) diff -cprN head/src/include/catalog/pg_trigger.h work/src/include/catalog/pg_trigger.h *** head/src/include/catalog/pg_trigger.h 2009-10-15 07:14:24.000000000 +0900 --- work/src/include/catalog/pg_trigger.h 2009-11-13 12:04:10.413811964 +0900 *************** CATALOG(pg_trigger,2620) *** 55,60 **** --- 55,61 ---- /* VARIABLE LENGTH FIELDS (note: these are not supposed to be null) */ int2vector tgattr; /* column numbers, if trigger is on columns */ bytea tgargs; /* first\000second\000tgnargs\000 */ + text tgqual; /* WHEN clause */ } FormData_pg_trigger; /* ---------------- *************** typedef FormData_pg_trigger *Form_pg_tri *** 68,74 **** * compiler constants for pg_trigger * ---------------- */ ! #define Natts_pg_trigger 15 #define Anum_pg_trigger_tgrelid 1 #define Anum_pg_trigger_tgname 2 #define Anum_pg_trigger_tgfoid 3 --- 69,75 ---- * compiler constants for pg_trigger * ---------------- */ ! #define Natts_pg_trigger 16 #define Anum_pg_trigger_tgrelid 1 #define Anum_pg_trigger_tgname 2 #define Anum_pg_trigger_tgfoid 3 *************** typedef FormData_pg_trigger *Form_pg_tri *** 84,89 **** --- 85,91 ---- #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 -cprN head/src/include/commands/trigger.h work/src/include/commands/trigger.h *** head/src/include/commands/trigger.h 2009-10-26 11:26:41.000000000 +0900 --- work/src/include/commands/trigger.h 2009-11-13 12:04:10.414837433 +0900 *************** extern PGDLLIMPORT int SessionReplicatio *** 104,110 **** #define TRIGGER_FIRES_ON_REPLICA 'R' #define TRIGGER_DISABLED 'D' ! extern Oid CreateTrigger(CreateTrigStmt *stmt, Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions); --- 104,110 ---- #define TRIGGER_FIRES_ON_REPLICA 'R' #define TRIGGER_DISABLED 'D' ! extern Oid CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions); diff -cprN head/src/include/nodes/execnodes.h work/src/include/nodes/execnodes.h *** head/src/include/nodes/execnodes.h 2009-10-26 11:26:41.000000000 +0900 --- work/src/include/nodes/execnodes.h 2009-11-13 12:04:10.414837433 +0900 *************** typedef struct EState *** 345,351 **** /* Stuff used for firing triggers: */ List *es_trig_target_relations; /* trigger-only ResultRelInfos */ ! TupleTableSlot *es_trig_tuple_slot; /* for trigger output tuples */ /* Parameter info: */ ParamListInfo es_param_list_info; /* values of external params */ --- 345,352 ---- /* 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_oldtup_slot; /* for trigger old tuples */ /* Parameter info: */ ParamListInfo es_param_list_info; /* values of external params */ diff -cprN head/src/include/nodes/parsenodes.h work/src/include/nodes/parsenodes.h *** head/src/include/nodes/parsenodes.h 2009-11-07 06:57:57.000000000 +0900 --- work/src/include/nodes/parsenodes.h 2009-11-13 12:04:10.414837433 +0900 *************** typedef struct CreateTrigStmt *** 1571,1576 **** --- 1571,1577 ---- /* 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; /* qualifications */ /* The following are used for constraint triggers (RI and unique checks) */ bool isconstraint; /* This is a constraint trigger */ diff -cprN head/src/include/utils/rel.h work/src/include/utils/rel.h *** head/src/include/utils/rel.h 2009-07-28 11:56:31.000000000 +0900 --- work/src/include/utils/rel.h 2009-11-13 12:04:10.415848373 +0900 *************** typedef struct Trigger *** 66,71 **** --- 66,72 ---- int16 tgnattr; int16 *tgattr; char **tgargs; + Node *tgqual; } Trigger; typedef struct TriggerDesc diff -cprN head/src/test/regress/expected/triggers.out work/src/test/regress/expected/triggers.out *** head/src/test/regress/expected/triggers.out 2009-10-15 07:14:25.000000000 +0900 --- work/src/test/regress/expected/triggers.out 2009-11-13 12:18:25.559859777 +0900 *************** SELECT * FROM main_table ORDER BY a, b; *** 322,327 **** --- 322,377 ---- | (8 rows) + -- Trigger 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'); + 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, 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; -- 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 *************** FOR EACH ROW EXECUTE PROCEDURE trigger_f *** 393,398 **** --- 443,463 ---- 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_when'); + ERROR: missing FROM-clause entry for table "old" + LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger... + ^ + 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_when'); + ERROR: missing FROM-clause entry for table "new" + LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger... + ^ + 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: missing FROM-clause entry for table "old" + LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECU... + ^ -- 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 -cprN head/src/test/regress/sql/triggers.sql work/src/test/regress/sql/triggers.sql *** head/src/test/regress/sql/triggers.sql 2009-10-15 07:14:25.000000000 +0900 --- work/src/test/regress/sql/triggers.sql 2009-11-13 12:04:10.415848373 +0900 *************** COPY main_table (a, b) FROM stdin; *** 254,259 **** --- 254,271 ---- SELECT * FROM main_table ORDER BY a, b; + -- Trigger 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'); + 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, 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; + -- Test column-level triggers DROP TRIGGER after_upd_row_trig ON main_table; *************** FOR EACH ROW EXECUTE PROCEDURE trigger_f *** 283,288 **** --- 295,307 ---- 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_when'); + 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_when'); + 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; -- this should succeed, but we'll roll it back to keep the triggers around