On Sat, 2009-10-10 at 00:04 +0300, Peter Eisentraut wrote:
> On Mon, 2009-09-14 at 18:58 +0900, Itagaki Takahiro wrote:
> > Itagaki Takahiro <[email protected]> wrote:
> >
> > > Ok, the attached patch implements standard-compliant version of
> > > column trigger.
> >
> > Here is an updated version of column-level trigger patch.
> > I forgot to adjust pg_get_triggerdef() in the previous version.
> > pg_dump also uses pg_get_triggerdef() instead of building
> > CREATE TRIGGER statements to avoid duplicated codes if the
> > server version is 8.5 or later.
>
> I have committed the parts involving pg_get_triggerdef and pg_dump. I
> will get to the actual column trigger functionality next.
Attached is a merged up patch with some slightly improved documentation.
I think the patch is almost ready now. One remaining issue is, in
TriggerEnabled() you apparently check the column list only if it is a
row trigger. But columns are supported for statement triggers as well
per SQL standard. Check please.
Btw., I might not get a chance to commit this within the next 48 hours.
If someone else wants to, go ahead.
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 6185a7d..f656cbf 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -122,6 +122,16 @@ CREATE TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTE
this specifies the event that will fire the trigger. Multiple
events can be specified using <literal>OR</literal>.
</para>
+
+ <para>
+ For <command>UPDATE</command> triggers, it is possible to
+ specify a list of columns using this syntax:
+<synopsis>
+UPDATE OF <replaceable>colname1</replaceable> [, <replaceable>colname2</replaceable>, ...]
+</synopsis>
+ The trigger will only fire if at least one of the listed columns
+ is mentioned as a target of the update.
+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 36bf050..ed956fe 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -36,10 +36,13 @@
performed. Triggers can be defined to execute either before or after any
<command>INSERT</command>, <command>UPDATE</command>, or
<command>DELETE</command> operation, either once per modified row,
- or once per <acronym>SQL</acronym> statement. Triggers can also fire
- for <command>TRUNCATE</command> statements. If a trigger event occurs,
- the trigger's function is called at the appropriate time to handle the
- event.
+ or once per <acronym>SQL</acronym>
+ statement. <command>UPDATE</command> triggers can moreover be set
+ to only fire if certain columns are mentioned in
+ the <literal>SET</literal> clause of the <command>UPDATE</command>
+ statement. Triggers can also fire for <command>TRUNCATE</command>
+ statements. If a trigger event occurs, the trigger's function is
+ called at the appropriate time to handle the event.
</para>
<para>
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 790cbdc..1af0c61 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -30,8 +30,11 @@
#include "executor/executor.h"
#include "executor/instrument.h"
#include "miscadmin.h"
+#include "nodes/bitmapset.h"
#include "nodes/makefuncs.h"
#include "parser/parse_func.h"
+#include "parser/parse_relation.h"
+#include "parser/parsetree.h"
#include "pgstat.h"
#include "storage/bufmgr.h"
#include "tcop/utility.h"
@@ -66,7 +69,8 @@ static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata,
MemoryContext per_tuple_context);
static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event,
bool row_trigger, HeapTuple oldtup, HeapTuple newtup,
- List *recheckIndexes);
+ List *recheckIndexes, Bitmapset *modifiedCols);
+static bool TriggerEnabled(Trigger *trigger, Bitmapset *modifiedCols);
/*
@@ -98,6 +102,8 @@ CreateTrigger(CreateTrigStmt *stmt,
bool checkPermissions)
{
int16 tgtype;
+ int ncolumns;
+ int2 *columns;
int2vector *tgattr;
Datum values[Natts_pg_trigger];
bool nulls[Natts_pg_trigger];
@@ -337,8 +343,39 @@ CreateTrigger(CreateTrigStmt *stmt,
CStringGetDatum(""));
}
- /* tgattr is currently always a zero-length array */
- tgattr = buildint2vector(NULL, 0);
+ /* build column references for UPDATE OF */
+ ncolumns = list_length(stmt->columns);
+ if (ncolumns == 0)
+ columns = NULL;
+ else
+ {
+ ListCell *cell;
+ int x = 0;
+
+ columns = (int2 *) palloc(ncolumns * sizeof(int2));
+
+ foreach (cell, stmt->columns)
+ {
+ char *name = strVal(lfirst(cell));
+ int attnum;
+ int y;
+
+ /* Lookup column name. System columns are not allowed. */
+ attnum = attnameAttNum(rel, name, false);
+
+ /* Check for duplicates */
+ for (y = x - 1; y >= 0; y--)
+ {
+ if (columns[y] == attnum)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg("column \"%s\" specified more than once", name)));
+ }
+
+ columns[x++] = attnum;
+ }
+ }
+ tgattr = buildint2vector(columns, ncolumns);
values[Anum_pg_trigger_tgattr - 1] = PointerGetDatum(tgattr);
tuple = heap_form_tuple(tgrel->rd_att, values, nulls);
@@ -434,6 +471,23 @@ CreateTrigger(CreateTrigStmt *stmt,
Assert(!OidIsValid(indexOid));
}
+ /* Add dependency on columns */
+ if (columns != NULL)
+ {
+ int i;
+ Form_pg_attribute *attrs;
+
+ attrs = RelationGetDescr(rel)->attrs;
+
+ referenced.classId = RelationRelationId;
+ referenced.objectId = RelationGetRelid(rel);
+ for (i = 0; i < ncolumns; i++)
+ {
+ referenced.objectSubId = attrs[columns[i] - 1]->attnum;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ }
+ }
+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
@@ -1626,18 +1680,9 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo)
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
HeapTuple newtuple;
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, NULL))
+ continue;
+
LocTriggerData.tg_trigger = trigger;
newtuple = ExecCallTriggerFunc(&LocTriggerData,
tgindx[i],
@@ -1659,7 +1704,7 @@ ExecASInsertTriggers(EState *estate, ResultRelInfo *relinfo)
if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0)
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT,
- false, NULL, NULL, NIL);
+ false, NULL, NULL, NIL, NULL);
}
HeapTuple
@@ -1685,18 +1730,9 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
{
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, NULL))
+ continue;
+
LocTriggerData.tg_trigtuple = oldtuple = newtuple;
LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
LocTriggerData.tg_trigger = trigger;
@@ -1721,7 +1757,7 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0)
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT,
- true, NULL, trigtuple, recheckIndexes);
+ true, NULL, trigtuple, recheckIndexes, NULL);
}
void
@@ -1757,18 +1793,9 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo)
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
HeapTuple newtuple;
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, NULL))
+ continue;
+
LocTriggerData.tg_trigger = trigger;
newtuple = ExecCallTriggerFunc(&LocTriggerData,
tgindx[i],
@@ -1790,7 +1817,7 @@ ExecASDeleteTriggers(EState *estate, ResultRelInfo *relinfo)
if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0)
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE,
- false, NULL, NULL, NIL);
+ false, NULL, NULL, NIL, NULL);
}
bool
@@ -1824,18 +1851,9 @@ ExecBRDeleteTriggers(EState *estate, PlanState *subplanstate,
{
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, NULL))
+ continue;
+
LocTriggerData.tg_trigtuple = trigtuple;
LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
LocTriggerData.tg_trigger = trigger;
@@ -1869,7 +1887,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
tupleid, NULL);
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE,
- true, trigtuple, NULL, NIL);
+ true, trigtuple, NULL, NIL, NULL);
heap_freetuple(trigtuple);
}
}
@@ -1907,18 +1925,9 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
HeapTuple newtuple;
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, NULL))
+ continue;
+
LocTriggerData.tg_trigger = trigger;
newtuple = ExecCallTriggerFunc(&LocTriggerData,
tgindx[i],
@@ -1940,7 +1949,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0)
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE,
- false, NULL, NULL, NIL);
+ false, NULL, NULL, NIL, NULL);
}
HeapTuple
@@ -1957,12 +1966,15 @@ ExecBRUpdateTriggers(EState *estate, PlanState *subplanstate,
HeapTuple intuple = newtuple;
TupleTableSlot *newSlot;
int i;
+ Bitmapset *modifiedCols;
trigtuple = GetTupleForTrigger(estate, subplanstate, relinfo, tupleid,
&newSlot);
if (trigtuple == NULL)
return NULL;
+ modifiedCols = rt_fetch(relinfo->ri_RangeTableIndex, estate->es_range_table)->modifiedCols;
+
/*
* In READ COMMITTED isolation level it's possible that newtuple was
* changed due to concurrent update. In that case we have a raw subplan
@@ -1980,18 +1992,9 @@ ExecBRUpdateTriggers(EState *estate, PlanState *subplanstate,
{
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, modifiedCols))
+ continue;
+
LocTriggerData.tg_trigtuple = trigtuple;
LocTriggerData.tg_newtuple = oldtuple = newtuple;
LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
@@ -2024,7 +2027,8 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
tupleid, NULL);
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE,
- true, trigtuple, newtuple, recheckIndexes);
+ true, trigtuple, newtuple, recheckIndexes,
+ rt_fetch(relinfo->ri_RangeTableIndex, estate->es_range_table)->modifiedCols);
heap_freetuple(trigtuple);
}
}
@@ -2062,18 +2066,9 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
HeapTuple newtuple;
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, NULL))
+ continue;
+
LocTriggerData.tg_trigger = trigger;
newtuple = ExecCallTriggerFunc(&LocTriggerData,
tgindx[i],
@@ -2095,7 +2090,7 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0)
AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE,
- false, NULL, NULL, NIL);
+ false, NULL, NULL, NIL, NULL);
}
@@ -3825,7 +3820,7 @@ AfterTriggerPendingOnRel(Oid relid)
static void
AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger,
HeapTuple oldtup, HeapTuple newtup,
- List *recheckIndexes)
+ List *recheckIndexes, Bitmapset *modifiedCols)
{
Relation rel = relinfo->ri_RelationDesc;
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -3927,19 +3922,8 @@ AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger,
{
Trigger *trigger = &trigdesc->triggers[tgindx[i]];
- /* Ignore disabled triggers */
- if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
- else /* ORIGIN or LOCAL role */
- {
- if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
- trigger->tgenabled == TRIGGER_DISABLED)
- continue;
- }
+ if (!TriggerEnabled(trigger, modifiedCols))
+ continue;
/*
* If this is an UPDATE of a PK table or FK table that does not change
@@ -4012,3 +3996,43 @@ AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger,
&new_event, &new_shared);
}
}
+
+static bool
+TriggerEnabled(Trigger *trigger, Bitmapset *modifiedCols)
+{
+ if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+ {
+ if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+ trigger->tgenabled == TRIGGER_DISABLED)
+ return false;
+ }
+ else /* ORIGIN or LOCAL role */
+ {
+ if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+ trigger->tgenabled == TRIGGER_DISABLED)
+ return false;
+ }
+
+ /* Check for column-level triggers */
+ if (trigger->tgnattr > 0 && modifiedCols != NULL &&
+ (trigger->tgtype & (TRIGGER_TYPE_UPDATE | TRIGGER_TYPE_ROW)) ==
+ (TRIGGER_TYPE_UPDATE | TRIGGER_TYPE_ROW))
+ {
+ int i;
+ bool modified;
+
+ modified = false;
+ for (i = 0; i < trigger->tgnattr; i++)
+ {
+ if (bms_is_member(trigger->tgattr[i] - FirstLowInvalidHeapAttributeNumber, modifiedCols))
+ {
+ modified = true;
+ break;
+ }
+ }
+ if (!modified)
+ return false;
+ }
+
+ return true;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d4acf60..9a2da19 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -251,7 +251,7 @@ static TypeName *TableFuncTypeName(List *columns);
%type <boolean> TriggerActionTime TriggerForSpec opt_trusted opt_restart_seqs
-%type <ival> TriggerEvents TriggerOneEvent
+%type <list> TriggerEvents TriggerOneEvent
%type <value> TriggerFuncArg
%type <str> relation_name copy_file_name
@@ -3240,7 +3240,8 @@ CreateTrigStmt:
n->args = $13;
n->before = $4;
n->row = $8;
- n->events = $5;
+ n->events = intVal(linitial($5));
+ n->columns = llast($5);
n->isconstraint = FALSE;
n->deferrable = FALSE;
n->initdeferred = FALSE;
@@ -3260,7 +3261,8 @@ CreateTrigStmt:
n->args = $18;
n->before = FALSE;
n->row = TRUE;
- n->events = $6;
+ n->events = intVal(linitial($6));
+ n->columns = llast($6);
n->isconstraint = TRUE;
n->deferrable = ($10 & 1) != 0;
n->initdeferred = ($10 & 2) != 0;
@@ -3279,17 +3281,22 @@ TriggerEvents:
{ $$ = $1; }
| TriggerEvents OR TriggerOneEvent
{
- if ($1 & $3)
+ int events1 = intVal(linitial($1));
+ int events2 = intVal(linitial($3));
+
+ if (events1 & events2)
parser_yyerror("duplicate trigger events specified");
- $$ = $1 | $3;
+ $$ = list_make2(makeInteger(events1 | events2),
+ list_concat(llast($1), llast($3)));
}
;
TriggerOneEvent:
- INSERT { $$ = TRIGGER_TYPE_INSERT; }
- | DELETE_P { $$ = TRIGGER_TYPE_DELETE; }
- | UPDATE { $$ = TRIGGER_TYPE_UPDATE; }
- | TRUNCATE { $$ = TRIGGER_TYPE_TRUNCATE; }
+ INSERT { $$ = list_make2(makeInteger(TRIGGER_TYPE_INSERT), NIL); }
+ | DELETE_P { $$ = list_make2(makeInteger(TRIGGER_TYPE_DELETE), NIL); }
+ | UPDATE { $$ = list_make2(makeInteger(TRIGGER_TYPE_UPDATE), NIL); }
+ | UPDATE OF columnList { $$ = list_make2(makeInteger(TRIGGER_TYPE_UPDATE), $3); }
+ | TRUNCATE { $$ = list_make2(makeInteger(TRIGGER_TYPE_TRUNCATE), NIL); }
;
TriggerForSpec:
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d20c893..529056d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -543,6 +543,20 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
appendStringInfo(&buf, " OR UPDATE");
else
appendStringInfo(&buf, " UPDATE");
+ if (trigrec->tgattr.dim1 > 0)
+ {
+ int i;
+
+ appendStringInfoString(&buf, " OF ");
+ for (i = 0; i < trigrec->tgattr.dim1; i++)
+ {
+ if (i > 0)
+ appendStringInfoString(&buf, ", ");
+ appendStringInfoString(&buf,
+ quote_identifier(get_relid_attribute_name(
+ trigrec->tgrelid, trigrec->tgattr.values[i])));
+ }
+ }
}
if (TRIGGER_FOR_TRUNCATE(trigrec->tgtype))
{
diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.h
index 64ca26f..17198a9 100644
--- a/src/include/catalog/pg_trigger.h
+++ b/src/include/catalog/pg_trigger.h
@@ -53,7 +53,7 @@ CATALOG(pg_trigger,2620)
int2 tgnargs; /* # of extra arguments in tgargs */
/* VARIABLE LENGTH FIELDS: */
- int2vector tgattr; /* reserved for column-specific triggers */
+ int2vector tgattr; /* column-specific triggers */
bytea tgargs; /* first\000second\000tgnargs\000 */
} FormData_pg_trigger;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index c629aca..bbd903f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1575,6 +1575,7 @@ typedef struct CreateTrigStmt
bool row; /* ROW/STATEMENT */
/* 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 */
/* The following are used for constraint triggers (RI and unique checks) */
bool isconstraint; /* This is a constraint trigger */
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index c0b7645..3e8c599 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -278,37 +278,37 @@ CREATE TABLE main_table (a int, b int);
COPY main_table (a,b) FROM stdin;
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
- RAISE NOTICE ''trigger_func() called: action = %, when = %, level = %'', TG_OP, TG_WHEN, TG_LEVEL;
+ RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;';
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
-FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
-FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
--
-CREATE TRIGGER before_upd_stmt_trig AFTER UPDATE ON main_table
-EXECUTE PROCEDURE trigger_func();
-CREATE TRIGGER before_upd_row_trig AFTER UPDATE ON main_table
-FOR EACH ROW EXECUTE PROCEDURE trigger_func();
+CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
+EXECUTE PROCEDURE trigger_func('after_upd_stmt');
+CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
INSERT INTO main_table DEFAULT VALUES;
-NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT
-NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
UPDATE main_table SET a = a + 1 WHERE b < 30;
-NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
-NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
-NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
-NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
-NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, 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
-- UPDATE that effects zero rows should still call per-statement trigger
UPDATE main_table SET a = a + 2 WHERE b > 100;
-NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
-- COPY should fire per-row and per-statement INSERT triggers
COPY main_table (a, b) FROM stdin;
-NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT
-NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT
+NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
+NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
SELECT * FROM main_table ORDER BY a, b;
a | b
----+----
@@ -322,6 +322,73 @@ SELECT * FROM main_table ORDER BY a, b;
|
(8 rows)
+-- Column-level triggers should only fire on after row-level updates
+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_func('before_upd_a_row');
+CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
+CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
+SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
+ pg_get_triggerdef
+-------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
+(1 row)
+
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
+ pg_get_triggerdef
+---------------------------------------------------------
+ CREATE TRIGGER after_upd_a_b_row_trig
+ AFTER UPDATE OF a, b ON main_table
+ FOR EACH ROW
+ EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
+(1 row)
+
+UPDATE main_table SET a = 50;
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+UPDATE main_table SET b = 10;
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
+NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
+ERROR: duplicate trigger events specified at or near "ON"
+LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta...
+ ^
+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');
+ERROR: column "a" specified more than once
+ALTER TABLE main_table DROP COLUMN b;
+ERROR: cannot drop table main_table column b because other objects depend on it
+DETAIL: trigger after_upd_b_row_trig on table main_table depends on table main_table column b
+trigger after_upd_a_b_row_trig on table main_table depends on table main_table column b
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP TRIGGER after_upd_a_b_row_trig ON main_table;
+DROP TRIGGER after_upd_b_row_trig ON main_table;
+ALTER TABLE main_table DROP COLUMN b;
-- Test enable/disable triggers
create table trigtest (i serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "trigtest_i_seq" for serial column "trigtest.i"
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 878adbb..ebef805 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -220,25 +220,25 @@ COPY main_table (a,b) FROM stdin;
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
- RAISE NOTICE ''trigger_func() called: action = %, when = %, level = %'', TG_OP, TG_WHEN, TG_LEVEL;
+ RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;';
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
-FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
-FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
--
-CREATE TRIGGER before_upd_stmt_trig AFTER UPDATE ON main_table
-EXECUTE PROCEDURE trigger_func();
+CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
+EXECUTE PROCEDURE trigger_func('after_upd_stmt');
-CREATE TRIGGER before_upd_row_trig AFTER UPDATE ON main_table
-FOR EACH ROW EXECUTE PROCEDURE trigger_func();
+CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
INSERT INTO main_table DEFAULT VALUES;
@@ -254,6 +254,32 @@ COPY main_table (a, b) FROM stdin;
SELECT * FROM main_table ORDER BY a, b;
+-- Column-level triggers should only fire on after row-level updates
+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_func('before_upd_a_row');
+CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
+CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
+
+SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
+
+UPDATE main_table SET a = 50;
+UPDATE main_table SET b = 10;
+
+CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
+FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
+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');
+
+ALTER TABLE main_table DROP COLUMN b;
+DROP TRIGGER after_upd_a_b_row_trig ON main_table;
+DROP TRIGGER after_upd_b_row_trig ON main_table;
+ALTER TABLE main_table DROP COLUMN b;
+
-- Test enable/disable triggers
create table trigtest (i serial primary key);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
