diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2010-10-10 13:43:33 -0400 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2010-10-10 13:45:07 -0400 |
| commit | 2ec993a7cbdd8e251817ac6bbc9a704ce8346f73 (patch) | |
| tree | 1568fb4b00b6fa7997755113a3d0bbfead45c1fb /src | |
| parent | f7b15b5098ee89a2628129fbbef9901bded9d27b (diff) | |
| download | postgresql-2ec993a7cbdd8e251817ac6bbc9a704ce8346f73.tar.gz postgresql-2ec993a7cbdd8e251817ac6bbc9a704ce8346f73.zip | |
Support triggers on views.
This patch adds the SQL-standard concept of an INSTEAD OF trigger, which
is fired instead of performing a physical insert/update/delete. The
trigger function is passed the entire old and/or new rows of the view,
and must figure out what to do to the underlying tables to implement
the update. So this feature can be used to implement updatable views
using trigger programming style rather than rule hacking.
In passing, this patch corrects the names of some columns in the
information_schema.triggers view. It seems the SQL committee renamed
them somewhere between SQL:99 and SQL:2003.
Dean Rasheed, reviewed by Bernd Helmle; some additional hacking by me.
Diffstat (limited to 'src')
37 files changed, 2329 insertions, 613 deletions
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 2b92e462538..b82b9b66fec 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -824,8 +824,8 @@ index_create(Oid heapRelationId, trigger->relation = heapRel; trigger->funcname = SystemFuncName("unique_key_recheck"); trigger->args = NIL; - trigger->before = false; trigger->row = true; + trigger->timing = TRIGGER_TYPE_AFTER; trigger->events = TRIGGER_TYPE_INSERT | TRIGGER_TYPE_UPDATE; trigger->columns = NIL; trigger->whenClause = NULL; diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index bd812220f14..f1c92635f04 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1934,18 +1934,22 @@ CREATE VIEW triggers AS position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47) AS character_data) AS action_statement, CAST( - CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END + -- hard-wired reference to TRIGGER_TYPE_ROW + CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END AS character_data) AS action_orientation, CAST( - CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END - AS character_data) AS condition_timing, - CAST(null AS sql_identifier) AS condition_reference_old_table, - CAST(null AS sql_identifier) AS condition_reference_new_table, - CAST(null AS sql_identifier) AS condition_reference_old_row, - CAST(null AS sql_identifier) AS condition_reference_new_row, + -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD + CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END + AS character_data) AS action_timing, + CAST(null AS sql_identifier) AS action_reference_old_table, + CAST(null AS sql_identifier) AS action_reference_new_table, + CAST(null AS sql_identifier) AS action_reference_old_row, + CAST(null AS sql_identifier) AS action_reference_new_row, CAST(null AS time_stamp) AS created FROM pg_namespace n, pg_class c, pg_trigger t, + -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE, + -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE (VALUES (4, 'INSERT'), (8, 'DELETE'), (16, 'UPDATE')) AS em (num, text) @@ -2233,9 +2237,23 @@ CREATE VIEW views AS THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, - CAST('NO' AS yes_or_no) AS is_trigger_updatable, - CAST('NO' AS yes_or_no) AS is_trigger_deletable, - CAST('NO' AS yes_or_no) AS is_trigger_insertable_into + CAST( + -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81) + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_trigger_updatable, + + CAST( + -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73) + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_trigger_deletable, + + CAST( + -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69) + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_trigger_insertable_into FROM pg_namespace nc, pg_class c diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index bfcfdfcfb82..b733f4ce33c 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -426,7 +426,7 @@ T211 Basic trigger capability 06 Support for run-time rules for the interaction T211 Basic trigger capability 07 TRIGGER privilege YES T211 Basic trigger capability 08 Multiple triggers for the same event are executed in the order in which they were created in the catalog NO intentionally omitted T212 Enhanced trigger capability YES -T213 INSTEAD OF triggers NO +T213 INSTEAD OF triggers YES T231 Sensitive cursors YES T241 START TRANSACTION statement YES T251 SET TRANSACTION statement: LOCAL option NO diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 44ed1136087..98110dfd2a3 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -2155,7 +2155,7 @@ CopyFrom(CopyState cstate) /* BEFORE ROW INSERT Triggers */ if (resultRelInfo->ri_TrigDesc && - resultRelInfo->ri_TrigDesc->n_before_row[TRIGGER_EVENT_INSERT] > 0) + resultRelInfo->ri_TrigDesc->trig_insert_before_row) { HeapTuple newtuple; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 403e55af917..c0097117545 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5690,8 +5690,8 @@ CreateFKCheckTrigger(RangeVar *myRel, Constraint *fkconstraint, fk_trigger = makeNode(CreateTrigStmt); fk_trigger->trigname = "RI_ConstraintTrigger"; fk_trigger->relation = myRel; - fk_trigger->before = false; fk_trigger->row = true; + fk_trigger->timing = TRIGGER_TYPE_AFTER; /* Either ON INSERT or ON UPDATE */ if (on_insert) @@ -5753,8 +5753,8 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, fk_trigger = makeNode(CreateTrigStmt); fk_trigger->trigname = "RI_ConstraintTrigger"; fk_trigger->relation = fkconstraint->pktable; - fk_trigger->before = false; fk_trigger->row = true; + fk_trigger->timing = TRIGGER_TYPE_AFTER; fk_trigger->events = TRIGGER_TYPE_DELETE; fk_trigger->columns = NIL; fk_trigger->whenClause = NULL; @@ -5806,8 +5806,8 @@ createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, fk_trigger = makeNode(CreateTrigStmt); fk_trigger->trigname = "RI_ConstraintTrigger"; fk_trigger->relation = fkconstraint->pktable; - fk_trigger->before = false; fk_trigger->row = true; + fk_trigger->timing = TRIGGER_TYPE_AFTER; fk_trigger->events = TRIGGER_TYPE_UPDATE; fk_trigger->columns = NIL; fk_trigger->whenClause = NULL; diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index f0b32ddd37f..d69fdcf410a 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -63,7 +63,7 @@ int SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN; /* Local function prototypes */ static void ConvertTriggerToFK(CreateTrigStmt *stmt, Oid funcoid); -static void InsertTrigger(TriggerDesc *trigdesc, Trigger *trigger, int indx); +static void SetTriggerFlags(TriggerDesc *trigdesc, Trigger *trigger); static HeapTuple GetTupleForTrigger(EState *estate, EPQState *epqstate, ResultRelInfo *relinfo, @@ -150,10 +150,45 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, */ rel = heap_openrv(stmt->relation, ShareRowExclusiveLock); - if (rel->rd_rel->relkind != RELKIND_RELATION) + /* + * Triggers must be on tables or views, and there are additional + * relation-type-specific restrictions. + */ + if (rel->rd_rel->relkind == RELKIND_RELATION) + { + /* Tables can't have INSTEAD OF triggers */ + if (stmt->timing != TRIGGER_TYPE_BEFORE && + stmt->timing != TRIGGER_TYPE_AFTER) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is a table", + RelationGetRelationName(rel)), + errdetail("Tables cannot have INSTEAD OF triggers."))); + } + else if (rel->rd_rel->relkind == RELKIND_VIEW) + { + /* + * Views can have INSTEAD OF triggers (which we check below are + * row-level), or statement-level BEFORE/AFTER triggers. + */ + if (stmt->timing != TRIGGER_TYPE_INSTEAD && stmt->row) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is a view", + RelationGetRelationName(rel)), + errdetail("Views cannot have row-level BEFORE or AFTER triggers."))); + /* Disallow TRUNCATE triggers on VIEWs */ + if (TRIGGER_FOR_TRUNCATE(stmt->events)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is a view", + RelationGetRelationName(rel)), + errdetail("Views cannot have TRUNCATE triggers."))); + } + else ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table", + errmsg("\"%s\" is not a table or view", RelationGetRelationName(rel)))); if (!allowSystemTableMods && IsSystemRelation(rel)) @@ -186,10 +221,9 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, /* Compute tgtype */ TRIGGER_CLEAR_TYPE(tgtype); - if (stmt->before) - TRIGGER_SETT_BEFORE(tgtype); if (stmt->row) TRIGGER_SETT_ROW(tgtype); + tgtype |= stmt->timing; tgtype |= stmt->events; /* Disallow ROW-level TRUNCATE triggers */ @@ -198,6 +232,23 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("TRUNCATE FOR EACH ROW triggers are not supported"))); + /* INSTEAD triggers must be row-level, and can't have WHEN or columns */ + if (TRIGGER_FOR_INSTEAD(tgtype)) + { + if (!TRIGGER_FOR_ROW(tgtype)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("INSTEAD OF triggers must be FOR EACH ROW"))); + if (stmt->whenClause) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("INSTEAD OF triggers cannot have WHEN conditions"))); + if (stmt->columns != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("INSTEAD OF triggers cannot have column lists"))); + } + /* * Parse the WHEN clause, if any */ @@ -1031,10 +1082,11 @@ RemoveTriggerById(Oid trigOid) rel = heap_open(relid, ShareRowExclusiveLock); - if (rel->rd_rel->relkind != RELKIND_RELATION) + if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_VIEW) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table", + errmsg("\"%s\" is not a table or view", RelationGetRelationName(rel)))); if (!allowSystemTableMods && IsSystemRelation(rel)) @@ -1472,7 +1524,7 @@ RelationBuildTriggers(Relation relation) trigdesc->triggers = triggers; trigdesc->numtriggers = numtrigs; for (i = 0; i < numtrigs; i++) - InsertTrigger(trigdesc, &(triggers[i]), i); + SetTriggerFlags(trigdesc, &(triggers[i])); /* Copy completed trigdesc into cache storage */ oldContext = MemoryContextSwitchTo(CacheMemoryContext); @@ -1484,84 +1536,65 @@ RelationBuildTriggers(Relation relation) } /* - * Insert the given trigger into the appropriate index list(s) for it - * - * To simplify storage management, we allocate each index list at the max - * possible size (trigdesc->numtriggers) if it's used at all. This does - * not waste space permanently since we're only building a temporary - * trigdesc at this point. + * Update the TriggerDesc's hint flags to include the specified trigger */ static void -InsertTrigger(TriggerDesc *trigdesc, Trigger *trigger, int indx) +SetTriggerFlags(TriggerDesc *trigdesc, Trigger *trigger) { - uint16 *n; - int **t, - **tp; - - if (TRIGGER_FOR_ROW(trigger->tgtype)) - { - /* ROW trigger */ - if (TRIGGER_FOR_BEFORE(trigger->tgtype)) - { - n = trigdesc->n_before_row; - t = trigdesc->tg_before_row; - } - else - { - n = trigdesc->n_after_row; - t = trigdesc->tg_after_row; - } - } - else - { - /* STATEMENT trigger */ - if (TRIGGER_FOR_BEFORE(trigger->tgtype)) - { - n = trigdesc->n_before_statement; - t = trigdesc->tg_before_statement; - } - else - { - n = trigdesc->n_after_statement; - t = trigdesc->tg_after_statement; - } - } - - if (TRIGGER_FOR_INSERT(trigger->tgtype)) - { - tp = &(t[TRIGGER_EVENT_INSERT]); - if (*tp == NULL) - *tp = (int *) palloc(trigdesc->numtriggers * sizeof(int)); - (*tp)[n[TRIGGER_EVENT_INSERT]] = indx; - (n[TRIGGER_EVENT_INSERT])++; - } - - if (TRIGGER_FOR_DELETE(trigger->tgtype)) - { - tp = &(t[TRIGGER_EVENT_DELETE]); - if (*tp == NULL) - *tp = (int *) palloc(trigdesc->numtriggers * sizeof(int)); - (*tp)[n[TRIGGER_EVENT_DELETE]] = indx; - (n[TRIGGER_EVENT_DELETE])++; - } - - if (TRIGGER_FOR_UPDATE(trigger->tgtype)) - { - tp = &(t[TRIGGER_EVENT_UPDATE]); - if (*tp == NULL) - *tp = (int *) palloc(trigdesc->numtriggers * sizeof(int)); - (*tp)[n[TRIGGER_EVENT_UPDATE]] = indx; - (n[TRIGGER_EVENT_UPDATE])++; - } - - if (TRIGGER_FOR_TRUNCATE(trigger->tgtype)) - { - tp = &(t[TRIGGER_EVENT_TRUNCATE]); - if (*tp == NULL) - *tp = (int *) palloc(trigdesc->numtriggers * sizeof(int)); - (*tp)[n[TRIGGER_EVENT_TRUNCATE]] = indx; - (n[TRIGGER_EVENT_TRUNCATE])++; - } + int16 tgtype = trigger->tgtype; + + trigdesc->trig_insert_before_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSERT); + trigdesc->trig_insert_after_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_INSERT); + trigdesc->trig_insert_instead_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_INSTEAD, TRIGGER_TYPE_INSERT); + trigdesc->trig_insert_before_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSERT); + trigdesc->trig_insert_after_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_INSERT); + trigdesc->trig_update_before_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_UPDATE); + trigdesc->trig_update_after_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_UPDATE); + trigdesc->trig_update_instead_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_INSTEAD, TRIGGER_TYPE_UPDATE); + trigdesc->trig_update_before_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_UPDATE); + trigdesc->trig_update_after_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_UPDATE); + trigdesc->trig_delete_before_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_DELETE); + trigdesc->trig_delete_after_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_DELETE); + trigdesc->trig_delete_instead_row |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_ROW, + TRIGGER_TYPE_INSTEAD, TRIGGER_TYPE_DELETE); + trigdesc->trig_delete_before_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_DELETE); + trigdesc->trig_delete_after_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_DELETE); + /* there are no row-level truncate triggers */ + trigdesc->trig_truncate_before_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_TRUNCATE); + trigdesc->trig_truncate_after_statement |= + TRIGGER_TYPE_MATCHES(tgtype, TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_AFTER, TRIGGER_TYPE_TRUNCATE); } /* @@ -1573,9 +1606,6 @@ TriggerDesc * CopyTriggerDesc(TriggerDesc *trigdesc) { TriggerDesc *newdesc; - uint16 *n; - int **t, - *tnew; Trigger *trigger; int i; @@ -1617,59 +1647,6 @@ CopyTriggerDesc(TriggerDesc *trigdesc) trigger++; } - n = newdesc->n_before_statement; - t = newdesc->tg_before_statement; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - { - if (n[i] > 0) - { - tnew = (int *) palloc(n[i] * sizeof(int)); - memcpy(tnew, t[i], n[i] * sizeof(int)); - t[i] = tnew; - } - else - t[i] = NULL; - } - n = newdesc->n_before_row; - t = newdesc->tg_before_row; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - { - if (n[i] > 0) - { - tnew = (int *) palloc(n[i] * sizeof(int)); - memcpy(tnew, t[i], n[i] * sizeof(int)); - t[i] = tnew; - } - else - t[i] = NULL; - } - n = newdesc->n_after_row; - t = newdesc->tg_after_row; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - { - if (n[i] > 0) - { - tnew = (int *) palloc(n[i] * sizeof(int)); - memcpy(tnew, t[i], n[i] * sizeof(int)); - t[i] = tnew; - } - else - t[i] = NULL; - } - n = newdesc->n_after_statement; - t = newdesc->tg_after_statement; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - { - if (n[i] > 0) - { - tnew = (int *) palloc(n[i] * sizeof(int)); - memcpy(tnew, t[i], n[i] * sizeof(int)); - t[i] = tnew; - } - else - t[i] = NULL; - } - return newdesc; } @@ -1679,30 +1656,12 @@ CopyTriggerDesc(TriggerDesc *trigdesc) void FreeTriggerDesc(TriggerDesc *trigdesc) { - int **t; Trigger *trigger; int i; if (trigdesc == NULL) return; - t = trigdesc->tg_before_statement; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - if (t[i] != NULL) - pfree(t[i]); - t = trigdesc->tg_before_row; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - if (t[i] != NULL) - pfree(t[i]); - t = trigdesc->tg_after_row; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - if (t[i] != NULL) - pfree(t[i]); - t = trigdesc->tg_after_statement; - for (i = 0; i < TRIGGER_NUM_EVENT_CLASSES; i++) - if (t[i] != NULL) - pfree(t[i]); - trigger = trigdesc->triggers; for (i = 0; i < trigdesc->numtriggers; i++) { @@ -1734,9 +1693,8 @@ equalTriggerDescs(TriggerDesc *trigdesc1, TriggerDesc *trigdesc2) j; /* - * We need not examine the "index" data, just the trigger array itself; if - * we have the same triggers with the same types, the derived index data - * should match. + * We need not examine the hint flags, just the trigger array itself; if + * we have the same triggers with the same types, the flags should match. * * As of 7.3 we assume trigger set ordering is significant in the * comparison; so we just compare corresponding slots of the two sets. @@ -1889,8 +1847,6 @@ void ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc; - int ntrigs; - int *tgindx; int i; TriggerData LocTriggerData; @@ -1898,11 +1854,7 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc == NULL) return; - - ntrigs = trigdesc->n_before_statement[TRIGGER_EVENT_INSERT]; - tgindx = trigdesc->tg_before_statement[TRIGGER_EVENT_INSERT]; - - if (ntrigs == 0) + if (!trigdesc->trig_insert_before_statement) return; LocTriggerData.type = T_TriggerData; @@ -1913,18 +1865,23 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_newtuple = NULL; LocTriggerData.tg_trigtuplebuf = InvalidBuffer; LocTriggerData.tg_newtuplebuf = InvalidBuffer; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; HeapTuple newtuple; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_INSERT)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -1941,7 +1898,7 @@ ExecASInsertTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) + if (trigdesc && trigdesc->trig_insert_after_statement) AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, false, NULL, NULL, NIL, NULL); } @@ -1951,8 +1908,6 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - int ntrigs = trigdesc->n_before_row[TRIGGER_EVENT_INSERT]; - int *tgindx = trigdesc->tg_before_row[TRIGGER_EVENT_INSERT]; HeapTuple newtuple = trigtuple; HeapTuple oldtuple; TriggerData LocTriggerData; @@ -1965,10 +1920,15 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, LocTriggerData.tg_relation = relinfo->ri_RelationDesc; LocTriggerData.tg_newtuple = NULL; LocTriggerData.tg_newtuplebuf = InvalidBuffer; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_ROW, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_INSERT)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, NULL, NULL, newtuple)) continue; @@ -1977,7 +1937,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, LocTriggerData.tg_trigtuplebuf = InvalidBuffer; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -1995,17 +1955,61 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) + if (trigdesc && trigdesc->trig_insert_after_row) AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT, true, NULL, trigtuple, recheckIndexes, NULL); } +HeapTuple +ExecIRInsertTriggers(EState *estate, ResultRelInfo *relinfo, + HeapTuple trigtuple) +{ + TriggerDesc *trigdesc = relinfo->ri_TrigDesc; + HeapTuple newtuple = trigtuple; + HeapTuple oldtuple; + TriggerData LocTriggerData; + int i; + + LocTriggerData.type = T_TriggerData; + LocTriggerData.tg_event = TRIGGER_EVENT_INSERT | + TRIGGER_EVENT_ROW | + TRIGGER_EVENT_INSTEAD; + LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_newtuple = NULL; + LocTriggerData.tg_newtuplebuf = InvalidBuffer; + for (i = 0; i < trigdesc->numtriggers; i++) + { + Trigger *trigger = &trigdesc->triggers[i]; + + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_ROW, + TRIGGER_TYPE_INSTEAD, + TRIGGER_TYPE_INSERT)) + continue; + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, NULL, newtuple)) + continue; + + LocTriggerData.tg_trigtuple = oldtuple = newtuple; + LocTriggerData.tg_trigtuplebuf = InvalidBuffer; + LocTriggerData.tg_trigger = trigger; + newtuple = ExecCallTriggerFunc(&LocTriggerData, + i, + relinfo->ri_TrigFunctions, + relinfo->ri_TrigInstrument, + GetPerTupleMemoryContext(estate)); + if (oldtuple != newtuple && oldtuple != trigtuple) + heap_freetuple(oldtuple); + if (newtuple == NULL) + break; + } + return newtuple; +} + void ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc; - int ntrigs; - int *tgindx; int i; TriggerData LocTriggerData; @@ -2013,11 +2017,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc == NULL) return; - - ntrigs = trigdesc->n_before_statement[TRIGGER_EVENT_DELETE]; - tgindx = trigdesc->tg_before_statement[TRIGGER_EVENT_DELETE]; - - if (ntrigs == 0) + if (!trigdesc->trig_delete_before_statement) return; LocTriggerData.type = T_TriggerData; @@ -2028,18 +2028,23 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_newtuple = NULL; LocTriggerData.tg_trigtuplebuf = InvalidBuffer; LocTriggerData.tg_newtuplebuf = InvalidBuffer; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; HeapTuple newtuple; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_DELETE)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -2056,7 +2061,7 @@ ExecASDeleteTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) + if (trigdesc && trigdesc->trig_delete_after_statement) AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE, false, NULL, NULL, NIL, NULL); } @@ -2067,8 +2072,6 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, ItemPointer tupleid) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - int ntrigs = trigdesc->n_before_row[TRIGGER_EVENT_DELETE]; - int *tgindx = trigdesc->tg_before_row[TRIGGER_EVENT_DELETE]; bool result = true; TriggerData LocTriggerData; HeapTuple trigtuple; @@ -2088,10 +2091,15 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, LocTriggerData.tg_relation = relinfo->ri_RelationDesc; LocTriggerData.tg_newtuple = NULL; LocTriggerData.tg_newtuplebuf = InvalidBuffer; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_ROW, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_DELETE)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, NULL, trigtuple, NULL)) continue; @@ -2100,7 +2108,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, LocTriggerData.tg_trigtuplebuf = InvalidBuffer; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -2123,7 +2131,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_DELETE] > 0) + if (trigdesc && trigdesc->trig_delete_after_row) { HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); @@ -2134,12 +2142,55 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, } } +bool +ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo, + HeapTuple trigtuple) +{ + TriggerDesc *trigdesc = relinfo->ri_TrigDesc; + TriggerData LocTriggerData; + HeapTuple rettuple; + int i; + + LocTriggerData.type = T_TriggerData; + LocTriggerData.tg_event = TRIGGER_EVENT_DELETE | + TRIGGER_EVENT_ROW | + TRIGGER_EVENT_INSTEAD; + LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_newtuple = NULL; + LocTriggerData.tg_newtuplebuf = InvalidBuffer; + for (i = 0; i < trigdesc->numtriggers; i++) + { + Trigger *trigger = &trigdesc->triggers[i]; + + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_ROW, + TRIGGER_TYPE_INSTEAD, + TRIGGER_TYPE_DELETE)) + continue; + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, trigtuple, NULL)) + continue; + + LocTriggerData.tg_trigtuple = trigtuple; + LocTriggerData.tg_trigtuplebuf = InvalidBuffer; + LocTriggerData.tg_trigger = trigger; + rettuple = ExecCallTriggerFunc(&LocTriggerData, + i, + relinfo->ri_TrigFunctions, + relinfo->ri_TrigInstrument, + GetPerTupleMemoryContext(estate)); + if (rettuple == NULL) + return false; /* Delete was suppressed */ + if (rettuple != trigtuple) + heap_freetuple(rettuple); + } + return true; +} + void ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc; - int ntrigs; - int *tgindx; int i; TriggerData LocTriggerData; Bitmapset *modifiedCols; @@ -2148,11 +2199,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc == NULL) return; - - ntrigs = trigdesc->n_before_statement[TRIGGER_EVENT_UPDATE]; - tgindx = trigdesc->tg_before_statement[TRIGGER_EVENT_UPDATE]; - - if (ntrigs == 0) + if (!trigdesc->trig_update_before_statement) return; modifiedCols = GetModifiedColumns(relinfo, estate); @@ -2165,18 +2212,23 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_newtuple = NULL; LocTriggerData.tg_trigtuplebuf = InvalidBuffer; LocTriggerData.tg_newtuplebuf = InvalidBuffer; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; HeapTuple newtuple; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_UPDATE)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, modifiedCols, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -2193,7 +2245,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) + if (trigdesc && trigdesc->trig_update_after_statement) AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE, false, NULL, NULL, NIL, GetModifiedColumns(relinfo, estate)); @@ -2205,8 +2257,6 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, ItemPointer tupleid, HeapTuple newtuple) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - int ntrigs = trigdesc->n_before_row[TRIGGER_EVENT_UPDATE]; - int *tgindx = trigdesc->tg_before_row[TRIGGER_EVENT_UPDATE]; TriggerData LocTriggerData; HeapTuple trigtuple; HeapTuple oldtuple; @@ -2235,10 +2285,15 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_ROW, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_UPDATE)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, modifiedCols, trigtuple, newtuple)) continue; @@ -2249,7 +2304,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, LocTriggerData.tg_newtuplebuf = InvalidBuffer; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -2269,7 +2324,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_UPDATE] > 0) + if (trigdesc && trigdesc->trig_update_after_row) { HeapTuple trigtuple = GetTupleForTrigger(estate, NULL, relinfo, tupleid, NULL); @@ -2281,12 +2336,57 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, } } +HeapTuple +ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo, + HeapTuple oldtuple, HeapTuple newtuple) +{ + TriggerDesc *trigdesc = relinfo->ri_TrigDesc; + TriggerData LocTriggerData; + HeapTuple intuple = newtuple; + HeapTuple rettuple; + int i; + + LocTriggerData.type = T_TriggerData; + LocTriggerData.tg_event = TRIGGER_EVENT_UPDATE | + TRIGGER_EVENT_ROW | + TRIGGER_EVENT_INSTEAD; + LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + for (i = 0; i < trigdesc->numtriggers; i++) + { + Trigger *trigger = &trigdesc->triggers[i]; + + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_ROW, + TRIGGER_TYPE_INSTEAD, + TRIGGER_TYPE_UPDATE)) + continue; + if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, + NULL, oldtuple, newtuple)) + continue; + + LocTriggerData.tg_trigtuple = oldtuple; + LocTriggerData.tg_newtuple = newtuple; + LocTriggerData.tg_trigtuplebuf = InvalidBuffer; + LocTriggerData.tg_newtuplebuf = InvalidBuffer; + LocTriggerData.tg_trigger = trigger; + rettuple = ExecCallTriggerFunc(&LocTriggerData, + i, + relinfo->ri_TrigFunctions, + relinfo->ri_TrigInstrument, + GetPerTupleMemoryContext(estate)); + if (newtuple != rettuple && newtuple != intuple) + heap_freetuple(newtuple); + newtuple = rettuple; + if (newtuple == NULL) + break; + } + return newtuple; +} + void ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc; - int ntrigs; - int *tgindx; int i; TriggerData LocTriggerData; @@ -2294,11 +2394,7 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc == NULL) return; - - ntrigs = trigdesc->n_before_statement[TRIGGER_EVENT_TRUNCATE]; - tgindx = trigdesc->tg_before_statement[TRIGGER_EVENT_TRUNCATE]; - - if (ntrigs == 0) + if (!trigdesc->trig_truncate_before_statement) return; LocTriggerData.type = T_TriggerData; @@ -2309,18 +2405,23 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_newtuple = NULL; LocTriggerData.tg_trigtuplebuf = InvalidBuffer; LocTriggerData.tg_newtuplebuf = InvalidBuffer; - for (i = 0; i < ntrigs; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; HeapTuple newtuple; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + TRIGGER_TYPE_STATEMENT, + TRIGGER_TYPE_BEFORE, + TRIGGER_TYPE_TRUNCATE)) + continue; if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event, NULL, NULL, NULL)) continue; LocTriggerData.tg_trigger = trigger; newtuple = ExecCallTriggerFunc(&LocTriggerData, - tgindx[i], + i, relinfo->ri_TrigFunctions, relinfo->ri_TrigInstrument, GetPerTupleMemoryContext(estate)); @@ -2337,7 +2438,7 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; - if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) + if (trigdesc && trigdesc->trig_truncate_after_statement) AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_TRUNCATE, false, NULL, NULL, NIL, NULL); } @@ -4250,9 +4351,9 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, TriggerDesc *trigdesc = relinfo->ri_TrigDesc; AfterTriggerEventData new_event; AfterTriggerSharedData new_shared; + int tgtype_event; + int tgtype_level; int i; - int ntriggers; - int *tgindx; /* * Check state. We use normal tests not Asserts because it is possible to @@ -4275,6 +4376,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, switch (event) { case TRIGGER_EVENT_INSERT: + tgtype_event = TRIGGER_TYPE_INSERT; if (row_trigger) { Assert(oldtup == NULL); @@ -4291,6 +4393,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, } break; case TRIGGER_EVENT_DELETE: + tgtype_event = TRIGGER_TYPE_DELETE; if (row_trigger) { Assert(oldtup != NULL); @@ -4307,6 +4410,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, } break; case TRIGGER_EVENT_UPDATE: + tgtype_event = TRIGGER_TYPE_UPDATE; if (row_trigger) { Assert(oldtup != NULL); @@ -4324,6 +4428,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, } break; case TRIGGER_EVENT_TRUNCATE: + tgtype_event = TRIGGER_TYPE_TRUNCATE; Assert(oldtup == NULL); Assert(newtup == NULL); ItemPointerSetInvalid(&(new_event.ate_ctid1)); @@ -4331,27 +4436,21 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, break; default: elog(ERROR, "invalid after-trigger event code: %d", event); + tgtype_event = 0; /* keep compiler quiet */ break; } - /* - * Scan the appropriate set of triggers - */ - if (row_trigger) - { - ntriggers = trigdesc->n_after_row[event]; - tgindx = trigdesc->tg_after_row[event]; - } - else - { - ntriggers = trigdesc->n_after_statement[event]; - tgindx = trigdesc->tg_after_statement[event]; - } + tgtype_level = (row_trigger ? TRIGGER_TYPE_ROW : TRIGGER_TYPE_STATEMENT); - for (i = 0; i < ntriggers; i++) + for (i = 0; i < trigdesc->numtriggers; i++) { - Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + Trigger *trigger = &trigdesc->triggers[i]; + if (!TRIGGER_TYPE_MATCHES(trigger->tgtype, + tgtype_level, + TRIGGER_TYPE_AFTER, + tgtype_event)) + continue; 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 20af966ddb0..69f3a28d415 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -877,9 +877,13 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, CmdType operation, int instrument_options) { + TriggerDesc *trigDesc = resultRelationDesc->trigdesc; + /* - * Check valid relkind ... parser and/or planner should have noticed this - * already, but let's make sure. + * Check valid relkind ... in most cases parser and/or planner should have + * noticed this already, but let's make sure. In the view case we do need + * a test here, because if the view wasn't rewritten by a rule, it had + * better have an INSTEAD trigger. */ switch (resultRelationDesc->rd_rel->relkind) { @@ -899,10 +903,36 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, RelationGetRelationName(resultRelationDesc)))); break; case RELKIND_VIEW: - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("cannot change view \"%s\"", - RelationGetRelationName(resultRelationDesc)))); + switch (operation) + { + case CMD_INSERT: + if (!trigDesc || !trigDesc->trig_insert_instead_row) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot insert into view \"%s\"", + RelationGetRelationName(resultRelationDesc)), + errhint("You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger."))); + break; + case CMD_UPDATE: + if (!trigDesc || !trigDesc->trig_update_instead_row) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot update view \"%s\"", + RelationGetRelationName(resultRelationDesc)), + errhint("You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger."))); + break; + case CMD_DELETE: + if (!trigDesc || !trigDesc->trig_delete_instead_row) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot delete from view \"%s\"", + RelationGetRelationName(resultRelationDesc)), + errhint("You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger."))); + break; + default: + elog(ERROR, "unrecognized CmdType: %d", (int) operation); + break; + } break; default: ereport(ERROR, @@ -921,7 +951,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_IndexRelationDescs = NULL; resultRelInfo->ri_IndexRelationInfo = NULL; /* make a copy so as not to depend on relcache info not changing... */ - resultRelInfo->ri_TrigDesc = CopyTriggerDesc(resultRelationDesc->trigdesc); + resultRelInfo->ri_TrigDesc = CopyTriggerDesc(trigDesc); if (resultRelInfo->ri_TrigDesc) { int n = resultRelInfo->ri_TrigDesc->numtriggers; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index a9958ebf394..541adaf6806 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -197,7 +197,7 @@ ExecInsert(TupleTableSlot *slot, /* BEFORE ROW INSERT Triggers */ if (resultRelInfo->ri_TrigDesc && - resultRelInfo->ri_TrigDesc->n_before_row[TRIGGER_EVENT_INSERT] > 0) + resultRelInfo->ri_TrigDesc->trig_insert_before_row) { HeapTuple newtuple; @@ -225,32 +225,66 @@ ExecInsert(TupleTableSlot *slot, } } - /* - * Check the constraints of the tuple - */ - if (resultRelationDesc->rd_att->constr) - ExecConstraints(resultRelInfo, slot, estate); + /* INSTEAD OF ROW INSERT Triggers */ + if (resultRelInfo->ri_TrigDesc && + resultRelInfo->ri_TrigDesc->trig_insert_instead_row) + { + HeapTuple newtuple; - /* - * insert the tuple - * - * Note: heap_insert returns the tid (location) of the new tuple in the - * t_self field. - */ - newId = heap_insert(resultRelationDesc, tuple, - estate->es_output_cid, 0, NULL); + newtuple = ExecIRInsertTriggers(estate, resultRelInfo, tuple); + + if (newtuple == NULL) /* "do nothing" */ + return NULL; + + if (newtuple != tuple) /* modified by Trigger(s) */ + { + /* + * Put the modified tuple into a slot for convenience of routines + * below. We assume the tuple was allocated in per-tuple memory + * context, and therefore will go away by itself. The tuple table + * slot should not try to clear it. + */ + TupleTableSlot *newslot = estate->es_trig_tuple_slot; + TupleDesc tupdesc = RelationGetDescr(resultRelationDesc); + + if (newslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(newslot, tupdesc); + ExecStoreTuple(newtuple, newslot, InvalidBuffer, false); + slot = newslot; + tuple = newtuple; + } + + newId = InvalidOid; + } + else + { + /* + * Check the constraints of the tuple + */ + if (resultRelationDesc->rd_att->constr) + ExecConstraints(resultRelInfo, slot, estate); + + /* + * insert the tuple + * + * Note: heap_insert returns the tid (location) of the new tuple in + * the t_self field. + */ + newId = heap_insert(resultRelationDesc, tuple, + estate->es_output_cid, 0, NULL); + + /* + * insert index entries for tuple + */ + if (resultRelInfo->ri_NumIndices > 0) + recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), + estate); + } (estate->es_processed)++; estate->es_lastoid = newId; setLastTid(&(tuple->t_self)); - /* - * insert index entries for tuple - */ - if (resultRelInfo->ri_NumIndices > 0) - recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), - estate); - /* AFTER ROW INSERT Triggers */ ExecARInsertTriggers(estate, resultRelInfo, tuple, recheckIndexes); @@ -268,13 +302,19 @@ ExecInsert(TupleTableSlot *slot, * ExecDelete * * DELETE is like UPDATE, except that we delete the tuple and no - * index modifications are needed + * index modifications are needed. + * + * When deleting from a table, tupleid identifies the tuple to + * delete and oldtuple is NULL. When deleting from a view, + * oldtuple is passed to the INSTEAD OF triggers and identifies + * what to delete, and tupleid is invalid. * * Returns RETURNING result if any, otherwise NULL. * ---------------------------------------------------------------- */ static TupleTableSlot * ExecDelete(ItemPointer tupleid, + HeapTupleHeader oldtuple, TupleTableSlot *planSlot, EPQState *epqstate, EState *estate) @@ -293,7 +333,7 @@ ExecDelete(ItemPointer tupleid, /* BEFORE ROW DELETE Triggers */ if (resultRelInfo->ri_TrigDesc && - resultRelInfo->ri_TrigDesc->n_before_row[TRIGGER_EVENT_DELETE] > 0) + resultRelInfo->ri_TrigDesc->trig_delete_before_row) { bool dodelete; @@ -304,69 +344,91 @@ ExecDelete(ItemPointer tupleid, return NULL; } - /* - * delete the tuple - * - * Note: if es_crosscheck_snapshot isn't InvalidSnapshot, we check that - * the row to be deleted is visible to that snapshot, and throw a can't- - * serialize error if not. This is a special-case behavior needed for - * referential integrity updates in transaction-snapshot mode transactions. - */ -ldelete:; - result = heap_delete(resultRelationDesc, tupleid, - &update_ctid, &update_xmax, - estate->es_output_cid, - estate->es_crosscheck_snapshot, - true /* wait for commit */ ); - switch (result) + /* INSTEAD OF ROW DELETE Triggers */ + if (resultRelInfo->ri_TrigDesc && + resultRelInfo->ri_TrigDesc->trig_delete_instead_row) { - case HeapTupleSelfUpdated: - /* already deleted by self; nothing to do */ + HeapTupleData tuple; + bool dodelete; + + Assert(oldtuple != NULL); + tuple.t_data = oldtuple; + tuple.t_len = HeapTupleHeaderGetDatumLength(oldtuple); + ItemPointerSetInvalid(&(tuple.t_self)); + tuple.t_tableOid = InvalidOid; + + dodelete = ExecIRDeleteTriggers(estate, resultRelInfo, &tuple); + + if (!dodelete) /* "do nothing" */ return NULL; + } + else + { + /* + * delete the tuple + * + * Note: if es_crosscheck_snapshot isn't InvalidSnapshot, we check + * that the row to be deleted is visible to that snapshot, and throw a + * can't-serialize error if not. This is a special-case behavior + * needed for referential integrity updates in transaction-snapshot + * mode transactions. + */ +ldelete:; + result = heap_delete(resultRelationDesc, tupleid, + &update_ctid, &update_xmax, + estate->es_output_cid, + estate->es_crosscheck_snapshot, + true /* wait for commit */ ); + switch (result) + { + case HeapTupleSelfUpdated: + /* already deleted by self; nothing to do */ + return NULL; - case HeapTupleMayBeUpdated: - break; + case HeapTupleMayBeUpdated: + break; - case HeapTupleUpdated: - if (IsolationUsesXactSnapshot()) - ereport(ERROR, - (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), - errmsg("could not serialize access due to concurrent update"))); - if (!ItemPointerEquals(tupleid, &update_ctid)) - { - TupleTableSlot *epqslot; - - epqslot = EvalPlanQual(estate, - epqstate, - resultRelationDesc, - resultRelInfo->ri_RangeTableIndex, - &update_ctid, - update_xmax); - if (!TupIsNull(epqslot)) + case HeapTupleUpdated: + if (IsolationUsesXactSnapshot()) + ereport(ERROR, + (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), + errmsg("could not serialize access due to concurrent update"))); + if (!ItemPointerEquals(tupleid, &update_ctid)) { - *tupleid = update_ctid; - goto ldelete; + TupleTableSlot *epqslot; + + epqslot = EvalPlanQual(estate, + epqstate, + resultRelationDesc, + resultRelInfo->ri_RangeTableIndex, + &update_ctid, + update_xmax); + if (!TupIsNull(epqslot)) + { + *tupleid = update_ctid; + goto ldelete; + } } - } - /* tuple already deleted; nothing to do */ - return NULL; + /* tuple already deleted; nothing to do */ + return NULL; - default: - elog(ERROR, "unrecognized heap_delete status: %u", result); - return NULL; + default: + elog(ERROR, "unrecognized heap_delete status: %u", result); + return NULL; + } + + /* + * Note: Normally one would think that we have to delete index tuples + * associated with the heap tuple now... + * + * ... but in POSTGRES, we have no need to do this because VACUUM will + * take care of it later. We can't delete index tuples immediately + * anyway, since the tuple is still visible to other transactions. + */ } (estate->es_processed)++; - /* - * Note: Normally one would think that we have to delete index tuples - * associated with the heap tuple now... - * - * ... but in POSTGRES, we have no need to do this because VACUUM will - * take care of it later. We can't delete index tuples immediately - * anyway, since the tuple is still visible to other transactions. - */ - /* AFTER ROW DELETE Triggers */ ExecARDeleteTriggers(estate, resultRelInfo, tupleid); @@ -382,10 +444,21 @@ ldelete:; HeapTupleData deltuple; Buffer delbuffer; - deltuple.t_self = *tupleid; - if (!heap_fetch(resultRelationDesc, SnapshotAny, - &deltuple, &delbuffer, false, NULL)) - elog(ERROR, "failed to fetch deleted tuple for DELETE RETURNING"); + if (oldtuple != NULL) + { + deltuple.t_data = oldtuple; + deltuple.t_len = HeapTupleHeaderGetDatumLength(oldtuple); + ItemPointerSetInvalid(&(deltuple.t_self)); + deltuple.t_tableOid = InvalidOid; + delbuffer = InvalidBuffer; + } + else + { + deltuple.t_self = *tupleid; + if (!heap_fetch(resultRelationDesc, SnapshotAny, + &deltuple, &delbuffer, false, NULL)) + elog(ERROR, "failed to fetch deleted tuple for DELETE RETURNING"); + } if (slot->tts_tupleDescriptor != RelationGetDescr(resultRelationDesc)) ExecSetSlotDescriptor(slot, RelationGetDescr(resultRelationDesc)); @@ -395,7 +468,8 @@ ldelete:; slot, planSlot); ExecClearTuple(slot); - ReleaseBuffer(delbuffer); + if (BufferIsValid(delbuffer)) + ReleaseBuffer(delbuffer); return rslot; } @@ -413,11 +487,17 @@ ldelete:; * is, we don't want to get stuck in an infinite loop * which corrupts your database.. * + * When updating a table, tupleid identifies the tuple to + * update and oldtuple is NULL. When updating a view, oldtuple + * is passed to the INSTEAD OF triggers and identifies what to + * update, and tupleid is invalid. + * * Returns RETURNING result if any, otherwise NULL. * ---------------------------------------------------------------- */ static TupleTableSlot * ExecUpdate(ItemPointer tupleid, + HeapTupleHeader oldtuple, TupleTableSlot *slot, TupleTableSlot *planSlot, EPQState *epqstate, @@ -451,7 +531,7 @@ ExecUpdate(ItemPointer tupleid, /* BEFORE ROW UPDATE Triggers */ if (resultRelInfo->ri_TrigDesc && - resultRelInfo->ri_TrigDesc->n_before_row[TRIGGER_EVENT_UPDATE] > 0) + resultRelInfo->ri_TrigDesc->trig_update_before_row) { HeapTuple newtuple; @@ -480,93 +560,135 @@ ExecUpdate(ItemPointer tupleid, } } - /* - * Check the constraints of the tuple - * - * If we generate a new candidate tuple after EvalPlanQual testing, we - * must loop back here and recheck constraints. (We don't need to redo - * triggers, however. If there are any BEFORE triggers then trigger.c - * will have done heap_lock_tuple to lock the correct tuple, so there's no - * need to do them again.) - */ -lreplace:; - if (resultRelationDesc->rd_att->constr) - ExecConstraints(resultRelInfo, slot, estate); - - /* - * replace the heap tuple - * - * Note: if es_crosscheck_snapshot isn't InvalidSnapshot, we check that - * the row to be updated is visible to that snapshot, and throw a can't- - * serialize error if not. This is a special-case behavior needed for - * referential integrity updates in transaction-snapshot mode transactions. - */ - result = heap_update(resultRelationDesc, tupleid, tuple, - &update_ctid, &update_xmax, - estate->es_output_cid, - estate->es_crosscheck_snapshot, - true /* wait for commit */ ); - switch (result) + /* INSTEAD OF ROW UPDATE Triggers */ + if (resultRelInfo->ri_TrigDesc && + resultRelInfo->ri_TrigDesc->trig_update_instead_row) { - case HeapTupleSelfUpdated: - /* already deleted by self; nothing to do */ + HeapTupleData oldtup; + HeapTuple newtuple; + + Assert(oldtuple != NULL); + oldtup.t_data = oldtuple; + oldtup.t_len = HeapTupleHeaderGetDatumLength(oldtuple); + ItemPointerSetInvalid(&(oldtup.t_self)); + oldtup.t_tableOid = InvalidOid; + + newtuple = ExecIRUpdateTriggers(estate, resultRelInfo, + &oldtup, tuple); + + if (newtuple == NULL) /* "do nothing" */ return NULL; - case HeapTupleMayBeUpdated: - break; + if (newtuple != tuple) /* modified by Trigger(s) */ + { + /* + * Put the modified tuple into a slot for convenience of routines + * below. We assume the tuple was allocated in per-tuple memory + * context, and therefore will go away by itself. The tuple table + * slot should not try to clear it. + */ + TupleTableSlot *newslot = estate->es_trig_tuple_slot; + TupleDesc tupdesc = RelationGetDescr(resultRelationDesc); - case HeapTupleUpdated: - if (IsolationUsesXactSnapshot()) - ereport(ERROR, - (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), - errmsg("could not serialize access due to concurrent update"))); - if (!ItemPointerEquals(tupleid, &update_ctid)) - { - TupleTableSlot *epqslot; - - epqslot = EvalPlanQual(estate, - epqstate, - resultRelationDesc, - resultRelInfo->ri_RangeTableIndex, - &update_ctid, - update_xmax); - if (!TupIsNull(epqslot)) + if (newslot->tts_tupleDescriptor != tupdesc) + ExecSetSlotDescriptor(newslot, tupdesc); + ExecStoreTuple(newtuple, newslot, InvalidBuffer, false); + slot = newslot; + tuple = newtuple; + } + } + else + { + /* + * Check the constraints of the tuple + * + * If we generate a new candidate tuple after EvalPlanQual testing, we + * must loop back here and recheck constraints. (We don't need to + * redo triggers, however. If there are any BEFORE triggers then + * trigger.c will have done heap_lock_tuple to lock the correct tuple, + * so there's no need to do them again.) + */ +lreplace:; + if (resultRelationDesc->rd_att->constr) + ExecConstraints(resultRelInfo, slot, estate); + + /* + * replace the heap tuple + * + * Note: if es_crosscheck_snapshot isn't InvalidSnapshot, we check + * that the row to be updated is visible to that snapshot, and throw a + * can't-serialize error if not. This is a special-case behavior + * needed for referential integrity updates in transaction-snapshot + * mode transactions. + */ + result = heap_update(resultRelationDesc, tupleid, tuple, + &update_ctid, &update_xmax, + estate->es_output_cid, + estate->es_crosscheck_snapshot, + true /* wait for commit */ ); + switch (result) + { + case HeapTupleSelfUpdated: + /* already deleted by self; nothing to do */ + return NULL; + + case HeapTupleMayBeUpdated: + break; + + case HeapTupleUpdated: + if (IsolationUsesXactSnapshot()) + ereport(ERROR, + (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), + errmsg("could not serialize access due to concurrent update"))); + if (!ItemPointerEquals(tupleid, &update_ctid)) { - *tupleid = update_ctid; - slot = ExecFilterJunk(resultRelInfo->ri_junkFilter, epqslot); - tuple = ExecMaterializeSlot(slot); - goto lreplace; + TupleTableSlot *epqslot; + + epqslot = EvalPlanQual(estate, + epqstate, + resultRelationDesc, + resultRelInfo->ri_RangeTableIndex, + &update_ctid, + update_xmax); + if (!TupIsNull(epqslot)) + { + *tupleid = update_ctid; + slot = ExecFilterJunk(resultRelInfo->ri_junkFilter, epqslot); + tuple = ExecMaterializeSlot(slot); + goto lreplace; + } } - } - /* tuple already deleted; nothing to do */ - return NULL; + /* tuple already deleted; nothing to do */ + return NULL; - default: - elog(ERROR, "unrecognized heap_update status: %u", result); - return NULL; - } + default: + elog(ERROR, "unrecognized heap_update status: %u", result); + return NULL; + } - (estate->es_processed)++; + /* + * Note: instead of having to update the old index tuples associated + * with the heap tuple, all we do is form and insert new index + * tuples. This is because UPDATEs are actually DELETEs and INSERTs, + * and index tuple deletion is done later by VACUUM (see notes in + * ExecDelete). All we do here is insert new index tuples. -cim + * 9/27/89 + */ - /* - * Note: instead of having to update the old index tuples associated with - * the heap tuple, all we do is form and insert new index tuples. This is - * because UPDATEs are actually DELETEs and INSERTs, and index tuple - * deletion is done later by VACUUM (see notes in ExecDelete). All we do - * here is insert new index tuples. -cim 9/27/89 - */ + /* + * insert index entries for tuple + * + * Note: heap_update returns the tid (location) of the new tuple in + * the t_self field. + * + * If it's a HOT update, we mustn't insert new index entries. + */ + if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple)) + recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), + estate); + } - /* - * insert index entries for tuple - * - * Note: heap_update returns the tid (location) of the new tuple in the - * t_self field. - * - * If it's a HOT update, we mustn't insert new index entries. - */ - if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple)) - recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), - estate); + (estate->es_processed)++; /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple, @@ -654,6 +776,7 @@ ExecModifyTable(ModifyTableState *node) TupleTableSlot *planSlot; ItemPointer tupleid = NULL; ItemPointerData tuple_ctid; + HeapTupleHeader oldtuple = NULL; /* * On first call, fire BEFORE STATEMENT triggers before proceeding. @@ -714,22 +837,37 @@ ExecModifyTable(ModifyTableState *node) if (junkfilter != NULL) { /* - * extract the 'ctid' junk attribute. + * extract the 'ctid' or 'wholerow' junk attribute. */ if (operation == CMD_UPDATE || operation == CMD_DELETE) { Datum datum; bool isNull; - datum = ExecGetJunkAttribute(slot, junkfilter->jf_junkAttNo, - &isNull); - /* shouldn't ever get a null result... */ - if (isNull) - elog(ERROR, "ctid is NULL"); - - tupleid = (ItemPointer) DatumGetPointer(datum); - tuple_ctid = *tupleid; /* be sure we don't free the ctid!! */ - tupleid = &tuple_ctid; + if (estate->es_result_relation_info->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION) + { + datum = ExecGetJunkAttribute(slot, + junkfilter->jf_junkAttNo, + &isNull); + /* shouldn't ever get a null result... */ + if (isNull) + elog(ERROR, "ctid is NULL"); + + tupleid = (ItemPointer) DatumGetPointer(datum); + tuple_ctid = *tupleid; /* be sure we don't free ctid!! */ + tupleid = &tuple_ctid; + } + else + { + datum = ExecGetJunkAttribute(slot, + junkfilter->jf_junkAttNo, + &isNull); + /* shouldn't ever get a null result... */ + if (isNull) + elog(ERROR, "wholerow is NULL"); + + oldtuple = DatumGetHeapTupleHeader(datum); + } } /* @@ -745,11 +883,11 @@ ExecModifyTable(ModifyTableState *node) slot = ExecInsert(slot, planSlot, estate); break; case CMD_UPDATE: - slot = ExecUpdate(tupleid, slot, planSlot, + slot = ExecUpdate(tupleid, oldtuple, slot, planSlot, &node->mt_epqstate, estate); break; case CMD_DELETE: - slot = ExecDelete(tupleid, planSlot, + slot = ExecDelete(tupleid, oldtuple, planSlot, &node->mt_epqstate, estate); break; default: @@ -934,8 +1072,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* * Initialize the junk filter(s) if needed. INSERT queries need a filter * if there are any junk attrs in the tlist. UPDATE and DELETE always - * need a filter, since there's always a junk 'ctid' attribute present --- - * no need to look first. + * need a filter, since there's always a junk 'ctid' or 'wholerow' + * attribute present --- no need to look first. * * If there are multiple result relations, each one needs its own junk * filter. Note multiple rels are only possible for UPDATE/DELETE, so we @@ -988,10 +1126,19 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) if (operation == CMD_UPDATE || operation == CMD_DELETE) { - /* For UPDATE/DELETE, find the ctid junk attr now */ - j->jf_junkAttNo = ExecFindJunkAttribute(j, "ctid"); - if (!AttributeNumberIsValid(j->jf_junkAttNo)) - elog(ERROR, "could not find junk ctid column"); + /* For UPDATE/DELETE, find the appropriate junk attr now */ + if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION) + { + j->jf_junkAttNo = ExecFindJunkAttribute(j, "ctid"); + if (!AttributeNumberIsValid(j->jf_junkAttNo)) + elog(ERROR, "could not find junk ctid column"); + } + else + { + j->jf_junkAttNo = ExecFindJunkAttribute(j, "wholerow"); + if (!AttributeNumberIsValid(j->jf_junkAttNo)) + elog(ERROR, "could not find junk wholerow column"); + } } resultRelInfo->ri_junkFilter = j; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 5bd0ef07502..2118a333a39 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3243,8 +3243,8 @@ _copyCreateTrigStmt(CreateTrigStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(funcname); COPY_NODE_FIELD(args); - COPY_SCALAR_FIELD(before); COPY_SCALAR_FIELD(row); + COPY_SCALAR_FIELD(timing); COPY_SCALAR_FIELD(events); COPY_NODE_FIELD(columns); COPY_NODE_FIELD(whenClause); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index c7dd42d1345..6bad724db66 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1698,8 +1698,8 @@ _equalCreateTrigStmt(CreateTrigStmt *a, CreateTrigStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(funcname); COMPARE_NODE_FIELD(args); - COMPARE_SCALAR_FIELD(before); COMPARE_SCALAR_FIELD(row); + COMPARE_SCALAR_FIELD(timing); COMPARE_SCALAR_FIELD(events); COMPARE_NODE_FIELD(columns); COMPARE_NODE_FIELD(whenClause); diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c index a8464a7aa0b..2c97c71472a 100644 --- a/src/backend/optimizer/prep/preptlist.c +++ b/src/backend/optimizer/prep/preptlist.c @@ -3,14 +3,14 @@ * preptlist.c * Routines to preprocess the parse tree target list * - * This module takes care of altering the query targetlist as needed for - * INSERT, UPDATE, and DELETE queries. For INSERT and UPDATE queries, - * the targetlist must contain an entry for each attribute of the target - * relation in the correct order. For both UPDATE and DELETE queries, - * we need a junk targetlist entry holding the CTID attribute --- the - * executor relies on this to find the tuple to be replaced/deleted. - * We may also need junk tlist entries for Vars used in the RETURNING list - * and row ID information needed for EvalPlanQual checking. + * For INSERT and UPDATE queries, the targetlist must contain an entry for + * each attribute of the target relation in the correct order. For all query + * types, we may need to add junk tlist entries for Vars used in the RETURNING + * list and row ID information needed for EvalPlanQual checking. + * + * NOTE: the rewriter's rewriteTargetListIU and rewriteTargetListUD + * routines also do preprocessing of the targetlist. The division of labor + * between here and there is a bit arbitrary and historical. * * * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group @@ -78,37 +78,6 @@ preprocess_targetlist(PlannerInfo *root, List *tlist) result_relation, range_table); /* - * for "update" and "delete" queries, add ctid of the result relation into - * the target list so that the ctid will propagate through execution and - * ExecutePlan() will be able to identify the right tuple to replace or - * delete. This extra field is marked "junk" so that it is not stored - * back into the tuple. - */ - if (command_type == CMD_UPDATE || command_type == CMD_DELETE) - { - TargetEntry *tle; - Var *var; - - var = makeVar(result_relation, SelfItemPointerAttributeNumber, - TIDOID, -1, 0); - - tle = makeTargetEntry((Expr *) var, - list_length(tlist) + 1, - pstrdup("ctid"), - true); - - /* - * For an UPDATE, expand_targetlist already created a fresh tlist. For - * DELETE, better do a listCopy so that we don't destructively modify - * the original tlist (is this really necessary?). - */ - if (command_type == CMD_DELETE) - tlist = list_copy(tlist); - - tlist = lappend(tlist, tle); - } - - /* * Add necessary junk columns for rowmarked rels. These values are needed * for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual * rechecking. While we are at it, store these junk attnos in the @@ -235,9 +204,6 @@ preprocess_targetlist(PlannerInfo *root, List *tlist) * Given a target list as generated by the parser and a result relation, * add targetlist entries for any missing attributes, and ensure the * non-junk attributes appear in proper field order. - * - * NOTE: if you are tempted to put more processing here, consider whether - * it shouldn't go in the rewriter's rewriteTargetList() instead. */ static List * expand_targetlist(List *tlist, int command_type, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 4054cb1bc7b..3a74fa50822 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -246,8 +246,8 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_ %type <str> OptSchemaName %type <list> OptSchemaEltList -%type <boolean> TriggerActionTime TriggerForSpec opt_trusted opt_restart_seqs - +%type <boolean> TriggerForSpec TriggerForType +%type <ival> TriggerActionTime %type <list> TriggerEvents TriggerOneEvent %type <value> TriggerFuncArg %type <node> TriggerWhen @@ -311,7 +311,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_ %type <fun_param_mode> arg_class %type <typnam> func_return func_type -%type <boolean> TriggerForType OptTemp +%type <boolean> OptTemp opt_trusted opt_restart_seqs %type <oncommit> OnCommitOption %type <node> for_locking_item @@ -3448,8 +3448,8 @@ CreateTrigStmt: n->relation = $7; n->funcname = $12; n->args = $14; - n->before = $4; n->row = $8; + n->timing = $4; n->events = intVal(linitial($5)); n->columns = (List *) lsecond($5); n->whenClause = $9; @@ -3469,8 +3469,8 @@ CreateTrigStmt: n->relation = $8; n->funcname = $17; n->args = $19; - n->before = FALSE; n->row = TRUE; + n->timing = TRIGGER_TYPE_AFTER; n->events = intVal(linitial($6)); n->columns = (List *) lsecond($6); n->whenClause = $14; @@ -3483,8 +3483,9 @@ CreateTrigStmt: ; TriggerActionTime: - BEFORE { $$ = TRUE; } - | AFTER { $$ = FALSE; } + BEFORE { $$ = TRIGGER_TYPE_BEFORE; } + | AFTER { $$ = TRIGGER_TYPE_AFTER; } + | INSTEAD OF { $$ = TRIGGER_TYPE_INSTEAD; } ; TriggerEvents: @@ -3525,7 +3526,7 @@ TriggerOneEvent: ; TriggerForSpec: - FOR TriggerForOpt TriggerForType + FOR TriggerForOptEach TriggerForType { $$ = $3; } @@ -3539,7 +3540,7 @@ TriggerForSpec: } ; -TriggerForOpt: +TriggerForOptEach: EACH {} | /*EMPTY*/ {} ; diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index e917554f5c5..31b25957b60 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -14,6 +14,7 @@ #include "postgres.h" #include "access/heapam.h" +#include "access/sysattr.h" #include "catalog/pg_type.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -43,14 +44,15 @@ static Query *rewriteRuleAction(Query *parsetree, CmdType event, bool *returning_flag); static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_index); -static void rewriteTargetList(Query *parsetree, Relation target_relation, - List **attrno_list); +static void rewriteTargetListIU(Query *parsetree, Relation target_relation, + List **attrno_list); static TargetEntry *process_matched_tle(TargetEntry *src_tle, TargetEntry *prior_tle, const char *attrName); static Node *get_assignment_input(Node *node); static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos); +static void rewriteTargetListUD(Query *parsetree, Relation target_relation); static void markQueryForLocking(Query *qry, Node *jtnode, bool forUpdate, bool noWait, bool pushedDown); static List *matchLocks(CmdType event, RuleLock *rulelocks, @@ -554,7 +556,7 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index) /* - * rewriteTargetList - rewrite INSERT/UPDATE targetlist into standard form + * rewriteTargetListIU - rewrite INSERT/UPDATE targetlist into standard form * * This has the following responsibilities: * @@ -566,7 +568,14 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index) * and UPDATE, replace explicit DEFAULT specifications with column default * expressions. * - * 2. Merge multiple entries for the same target attribute, or declare error + * 2. For an UPDATE on a view, add tlist entries for any unassigned-to + * attributes, assigning them their old values. These will later get + * expanded to the output values of the view. (This is equivalent to what + * the planner's expand_targetlist() will do for UPDATE on a regular table, + * but it's more convenient to do it here while we still have easy access + * to the view's original RT index.) + * + * 3. Merge multiple entries for the same target attribute, or declare error * if we can't. Multiple entries are only allowed for INSERT/UPDATE of * portions of an array or record field, for example * UPDATE table SET foo[2] = 42, foo[4] = 43; @@ -574,13 +583,13 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index) * the expression we want to produce in this case is like * foo = array_set(array_set(foo, 2, 42), 4, 43) * - * 3. Sort the tlist into standard order: non-junk fields in order by resno, + * 4. Sort the tlist into standard order: non-junk fields in order by resno, * then junk fields (these in no particular order). * - * We must do items 1 and 2 before firing rewrite rules, else rewritten - * references to NEW.foo will produce wrong or incomplete results. Item 3 + * We must do items 1,2,3 before firing rewrite rules, else rewritten + * references to NEW.foo will produce wrong or incomplete results. Item 4 * is not needed for rewriting, but will be needed by the planner, and we - * can do it essentially for free while handling items 1 and 2. + * can do it essentially for free while handling the other items. * * If attrno_list isn't NULL, we return an additional output besides the * rewritten targetlist: an integer list of the assigned-to attnums, in @@ -588,8 +597,8 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index) * processing VALUES RTEs. */ static void -rewriteTargetList(Query *parsetree, Relation target_relation, - List **attrno_list) +rewriteTargetListIU(Query *parsetree, Relation target_relation, + List **attrno_list) { CmdType commandType = parsetree->commandType; TargetEntry **new_tles; @@ -724,6 +733,27 @@ rewriteTargetList(Query *parsetree, Relation target_relation, false); } + /* + * For an UPDATE on a view, provide a dummy entry whenever there is + * no explicit assignment. + */ + if (new_tle == NULL && commandType == CMD_UPDATE && + target_relation->rd_rel->relkind == RELKIND_VIEW) + { + Node *new_expr; + + new_expr = (Node *) makeVar(parsetree->resultRelation, + attrno, + att_tup->atttypid, + att_tup->atttypmod, + 0); + + new_tle = makeTargetEntry((Expr *) new_expr, + attrno, + pstrdup(NameStr(att_tup->attname)), + false); + } + if (new_tle) new_tlist = lappend(new_tlist, new_tle); } @@ -985,8 +1015,8 @@ searchForDefault(RangeTblEntry *rte) /* * When processing INSERT ... VALUES with a VALUES RTE (ie, multiple VALUES * lists), we have to replace any DEFAULT items in the VALUES lists with - * the appropriate default expressions. The other aspects of rewriteTargetList - * need be applied only to the query's targetlist proper. + * the appropriate default expressions. The other aspects of targetlist + * rewriting need be applied only to the query's targetlist proper. * * Note that we currently can't support subscripted or field assignment * in the multi-VALUES case. The targetlist will contain simple Vars @@ -1068,6 +1098,62 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos) /* + * rewriteTargetListUD - rewrite UPDATE/DELETE targetlist as needed + * + * This function adds a "junk" TLE that is needed to allow the executor to + * find the original row for the update or delete. When the target relation + * is a regular table, the junk TLE emits the ctid attribute of the original + * row. When the target relation is a view, there is no ctid, so we instead + * emit a whole-row Var that will contain the "old" values of the view row. + * + * For UPDATE queries, this is applied after rewriteTargetListIU. The + * ordering isn't actually critical at the moment. + */ +static void +rewriteTargetListUD(Query *parsetree, Relation target_relation) +{ + Var *var; + const char *attrname; + TargetEntry *tle; + + if (target_relation->rd_rel->relkind == RELKIND_RELATION) + { + /* + * Emit CTID so that executor can find the row to update or delete. + */ + var = makeVar(parsetree->resultRelation, + SelfItemPointerAttributeNumber, + TIDOID, + -1, + 0); + + attrname = "ctid"; + } + else + { + /* + * Emit whole-row Var so that executor will have the "old" view row + * to pass to the INSTEAD OF trigger. + */ + var = makeVar(parsetree->resultRelation, + InvalidAttrNumber, + RECORDOID, + -1, + 0); + + attrname = "wholerow"; + } + + tle = makeTargetEntry((Expr *) var, + list_length(parsetree->targetList) + 1, + pstrdup(attrname), + true); + + parsetree->targetList = lappend(parsetree->targetList, tle); +} + + +/* * matchLocks - * match the list of locks and returns the matching rules */ @@ -1157,6 +1243,67 @@ ApplyRetrieveRule(Query *parsetree, if (!relation_level) elog(ERROR, "cannot handle per-attribute ON SELECT rule"); + if (rt_index == parsetree->resultRelation) + { + /* + * We have a view as the result relation of the query, and it wasn't + * rewritten by any rule. This case is supported if there is an + * INSTEAD OF trigger that will trap attempts to insert/update/delete + * view rows. The executor will check that; for the moment just plow + * ahead. We have two cases: + * + * For INSERT, we needn't do anything. The unmodified RTE will serve + * fine as the result relation. + * + * For UPDATE/DELETE, we need to expand the view so as to have source + * data for the operation. But we also need an unmodified RTE to + * serve as the target. So, copy the RTE and add the copy to the + * rangetable. Note that the copy does not get added to the jointree. + * Also note that there's a hack in fireRIRrules to avoid calling + * this function again when it arrives at the copied RTE. + */ + if (parsetree->commandType == CMD_INSERT) + return parsetree; + else if (parsetree->commandType == CMD_UPDATE || + parsetree->commandType == CMD_DELETE) + { + RangeTblEntry *newrte; + + rte = rt_fetch(rt_index, parsetree->rtable); + newrte = copyObject(rte); + parsetree->rtable = lappend(parsetree->rtable, newrte); + parsetree->resultRelation = list_length(parsetree->rtable); + + /* + * There's no need to do permissions checks twice, so wipe out + * the permissions info for the original RTE (we prefer to keep + * the bits set on the result RTE). + */ + rte->requiredPerms = 0; + rte->checkAsUser = InvalidOid; + rte->selectedCols = NULL; + rte->modifiedCols = NULL; + + /* + * For the most part, Vars referencing the view should remain as + * they are, meaning that they implicitly represent OLD values. + * But in the RETURNING list if any, we want such Vars to + * represent NEW values, so change them to reference the new RTE. + * + * Since ChangeVarNodes scribbles on the tree in-place, copy the + * RETURNING list first for safety. + */ + parsetree->returningList = copyObject(parsetree->returningList); + ChangeVarNodes((Node *) parsetree->returningList, rt_index, + parsetree->resultRelation, 0); + + /* Now, continue with expanding the original view RTE */ + } + else + elog(ERROR, "unrecognized commandType: %d", + (int) parsetree->commandType); + } + /* * If FOR UPDATE/SHARE of view, be sure we get right initial lock on the * relations it references. @@ -1178,8 +1325,8 @@ ApplyRetrieveRule(Query *parsetree, rule_action = fireRIRrules(rule_action, activeRIRs, forUpdatePushedDown); /* - * VIEWs are really easy --- just plug the view query in as a subselect, - * replacing the relation's original RTE. + * Now, plug the view query in as a subselect, replacing the relation's + * original RTE. */ rte = rt_fetch(rt_index, parsetree->rtable); @@ -1320,6 +1467,7 @@ fireRIRonSubLink(Node *node, List *activeRIRs) static Query * fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown) { + int origResultRelation = parsetree->resultRelation; int rt_index; ListCell *lc; @@ -1372,6 +1520,14 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown) continue; /* + * Also, if this is a new result relation introduced by + * ApplyRetrieveRule, we don't want to do anything more with it. + */ + if (rt_index == parsetree->resultRelation && + rt_index != origResultRelation) + continue; + + /* * We can use NoLock here since either the parser or * AcquireRewriteLocks should have locked the rel already. */ @@ -1634,7 +1790,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events) List *rewritten = NIL; /* - * If the statement is an update, insert or delete - fire rules on it. + * If the statement is an insert, update, or delete, adjust its targetlist + * as needed, and then fire INSERT/UPDATE/DELETE rules on it. * * SELECT rules are handled later when we have all the queries that should * get executed. Also, utilities aren't rewritten at all (do we still @@ -1659,13 +1816,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events) rt_entry_relation = heap_open(rt_entry->relid, NoLock); /* - * If it's an INSERT or UPDATE, rewrite the targetlist into standard - * form. This will be needed by the planner anyway, and doing it now - * ensures that any references to NEW.field will behave sanely. + * Rewrite the targetlist as needed for the command type. */ - if (event == CMD_UPDATE) - rewriteTargetList(parsetree, rt_entry_relation, NULL); - else if (event == CMD_INSERT) + if (event == CMD_INSERT) { RangeTblEntry *values_rte = NULL; @@ -1692,16 +1845,27 @@ RewriteQuery(Query *parsetree, List *rewrite_events) List *attrnos; /* Process the main targetlist ... */ - rewriteTargetList(parsetree, rt_entry_relation, &attrnos); + rewriteTargetListIU(parsetree, rt_entry_relation, &attrnos); /* ... and the VALUES expression lists */ rewriteValuesRTE(values_rte, rt_entry_relation, attrnos); } else { /* Process just the main targetlist */ - rewriteTargetList(parsetree, rt_entry_relation, NULL); + rewriteTargetListIU(parsetree, rt_entry_relation, NULL); } } + else if (event == CMD_UPDATE) + { + rewriteTargetListIU(parsetree, rt_entry_relation, NULL); + rewriteTargetListUD(parsetree, rt_entry_relation); + } + else if (event == CMD_DELETE) + { + rewriteTargetListUD(parsetree, rt_entry_relation); + } + else + elog(ERROR, "unrecognized commandType: %d", (int) event); /* * Collect and apply the appropriate rules. @@ -1850,7 +2014,7 @@ List * QueryRewrite(Query *parsetree) { List *querylist; - List *results = NIL; + List *results; ListCell *l; CmdType origCmdType; bool foundOriginalQuery; @@ -1868,50 +2032,12 @@ QueryRewrite(Query *parsetree) * * Apply all the RIR rules on each query */ + results = NIL; foreach(l, querylist) { Query *query = (Query *) lfirst(l); query = fireRIRrules(query, NIL, false); - - /* - * If the query target was rewritten as a view, complain. - */ - if (query->resultRelation) - { - RangeTblEntry *rte = rt_fetch(query->resultRelation, - query->rtable); - - if (rte->rtekind == RTE_SUBQUERY) - { - switch (query->commandType) - { - case CMD_INSERT: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot insert into a view"), - errhint("You need an unconditional ON INSERT DO INSTEAD rule."))); - break; - case CMD_UPDATE: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot update a view"), - errhint("You need an unconditional ON UPDATE DO INSTEAD rule."))); - break; - case CMD_DELETE: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot delete from a view"), - errhint("You need an unconditional ON DELETE DO INSTEAD rule."))); - break; - default: - elog(ERROR, "unrecognized commandType: %d", - (int) query->commandType); - break; - } - } - } - results = lappend(results, query); } diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c index 5db2522aedd..7440a41898f 100644 --- a/src/backend/rewrite/rewriteManip.c +++ b/src/backend/rewrite/rewriteManip.c @@ -1262,7 +1262,7 @@ ResolveNew_callback(Var *var, /* Normal case referencing one targetlist element */ tle = get_tle_by_resno(rcon->targetlist, var->varattno); - if (tle == NULL) + if (tle == NULL || tle->resjunk) { /* Failed to find column in insert/update tlist */ if (rcon->event == CMD_UPDATE) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 578b9ce2b7b..f1c1d04ee09 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -545,8 +545,13 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) if (TRIGGER_FOR_BEFORE(trigrec->tgtype)) appendStringInfo(&buf, "BEFORE"); - else + else if (TRIGGER_FOR_AFTER(trigrec->tgtype)) appendStringInfo(&buf, "AFTER"); + else if (TRIGGER_FOR_INSTEAD(trigrec->tgtype)) + appendStringInfo(&buf, "INSTEAD OF"); + else + elog(ERROR, "unexpected tgtype value: %d", trigrec->tgtype); + if (TRIGGER_FOR_INSERT(trigrec->tgtype)) { appendStringInfo(&buf, " INSERT"); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index ff7d97a064b..6a4557b4861 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -12096,11 +12096,19 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo) appendPQExpBuffer(query, "\n "); /* Trigger type */ - findx = 0; if (TRIGGER_FOR_BEFORE(tginfo->tgtype)) appendPQExpBuffer(query, "BEFORE"); - else + else if (TRIGGER_FOR_AFTER(tginfo->tgtype)) appendPQExpBuffer(query, "AFTER"); + else if (TRIGGER_FOR_INSTEAD(tginfo->tgtype)) + appendPQExpBuffer(query, "INSTEAD OF"); + else + { + write_msg(NULL, "unexpected tgtype value: %d\n", tginfo->tgtype); + exit_nicely(); + } + + findx = 0; if (TRIGGER_FOR_INSERT(tginfo->tgtype)) { appendPQExpBuffer(query, " INSERT"); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 83e9845103a..57d74e14d75 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1820,10 +1820,17 @@ describeOneTableDetails(const char *schemaname, } PQclear(result); } + } + + /* + * Print triggers next, if any (but only user-defined triggers). This + * could apply to either a table or a view. + */ + if (tableinfo.hastriggers) + { + PGresult *result; + int tuples; - /* print triggers (but only user-defined triggers) */ - if (tableinfo.hastriggers) - { printfPQExpBuffer(&buf, "SELECT t.tgname, " "pg_catalog.pg_get_triggerdef(t.oid%s), " @@ -1934,7 +1941,15 @@ describeOneTableDetails(const char *schemaname, } } PQclear(result); - } + } + + /* + * Finish printing the footer information about a table. + */ + if (tableinfo.relkind == 'r') + { + PGresult *result; + int tuples; /* print inherited tables */ printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno", oid); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index c9212ab584b..910474cdcfc 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201009281 +#define CATALOG_VERSION_NO 201010101 #endif diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.h index 46285ac3038..4eb72f20916 100644 --- a/src/include/catalog/pg_trigger.h +++ b/src/include/catalog/pg_trigger.h @@ -38,7 +38,7 @@ CATALOG(pg_trigger,2620) Oid tgrelid; /* relation trigger is attached to */ NameData tgname; /* trigger's name */ Oid tgfoid; /* OID of function to be called */ - int2 tgtype; /* BEFORE/AFTER UPDATE/DELETE/INSERT + int2 tgtype; /* BEFORE/AFTER/INSTEAD, UPDATE/DELETE/INSERT, * ROW/STATEMENT; see below */ char tgenabled; /* trigger's firing configuration WRT * session_replication_role */ @@ -91,22 +91,49 @@ typedef FormData_pg_trigger *Form_pg_trigger; #define TRIGGER_TYPE_DELETE (1 << 3) #define TRIGGER_TYPE_UPDATE (1 << 4) #define TRIGGER_TYPE_TRUNCATE (1 << 5) +#define TRIGGER_TYPE_INSTEAD (1 << 6) + +#define TRIGGER_TYPE_LEVEL_MASK (TRIGGER_TYPE_ROW) +#define TRIGGER_TYPE_STATEMENT 0 + +/* Note bits within TRIGGER_TYPE_TIMING_MASK aren't adjacent */ +#define TRIGGER_TYPE_TIMING_MASK \ + (TRIGGER_TYPE_BEFORE | TRIGGER_TYPE_INSTEAD) +#define TRIGGER_TYPE_AFTER 0 + +#define TRIGGER_TYPE_EVENT_MASK \ + (TRIGGER_TYPE_INSERT | TRIGGER_TYPE_DELETE | TRIGGER_TYPE_UPDATE | TRIGGER_TYPE_TRUNCATE) /* Macros for manipulating tgtype */ #define TRIGGER_CLEAR_TYPE(type) ((type) = 0) #define TRIGGER_SETT_ROW(type) ((type) |= TRIGGER_TYPE_ROW) +#define TRIGGER_SETT_STATEMENT(type) ((type) |= TRIGGER_TYPE_STATEMENT) #define TRIGGER_SETT_BEFORE(type) ((type) |= TRIGGER_TYPE_BEFORE) +#define TRIGGER_SETT_AFTER(type) ((type) |= TRIGGER_TYPE_AFTER) +#define TRIGGER_SETT_INSTEAD(type) ((type) |= TRIGGER_TYPE_INSTEAD) #define TRIGGER_SETT_INSERT(type) ((type) |= TRIGGER_TYPE_INSERT) #define TRIGGER_SETT_DELETE(type) ((type) |= TRIGGER_TYPE_DELETE) #define TRIGGER_SETT_UPDATE(type) ((type) |= TRIGGER_TYPE_UPDATE) #define TRIGGER_SETT_TRUNCATE(type) ((type) |= TRIGGER_TYPE_TRUNCATE) #define TRIGGER_FOR_ROW(type) ((type) & TRIGGER_TYPE_ROW) -#define TRIGGER_FOR_BEFORE(type) ((type) & TRIGGER_TYPE_BEFORE) +#define TRIGGER_FOR_BEFORE(type) (((type) & TRIGGER_TYPE_TIMING_MASK) == TRIGGER_TYPE_BEFORE) +#define TRIGGER_FOR_AFTER(type) (((type) & TRIGGER_TYPE_TIMING_MASK) == TRIGGER_TYPE_AFTER) +#define TRIGGER_FOR_INSTEAD(type) (((type) & TRIGGER_TYPE_TIMING_MASK) == TRIGGER_TYPE_INSTEAD) #define TRIGGER_FOR_INSERT(type) ((type) & TRIGGER_TYPE_INSERT) #define TRIGGER_FOR_DELETE(type) ((type) & TRIGGER_TYPE_DELETE) #define TRIGGER_FOR_UPDATE(type) ((type) & TRIGGER_TYPE_UPDATE) #define TRIGGER_FOR_TRUNCATE(type) ((type) & TRIGGER_TYPE_TRUNCATE) +/* + * Efficient macro for checking if tgtype matches a particular level + * (TRIGGER_TYPE_ROW or TRIGGER_TYPE_STATEMENT), timing (TRIGGER_TYPE_BEFORE, + * TRIGGER_TYPE_AFTER or TRIGGER_TYPE_INSTEAD), and event (TRIGGER_TYPE_INSERT, + * TRIGGER_TYPE_DELETE, TRIGGER_TYPE_UPDATE, or TRIGGER_TYPE_TRUNCATE). Note + * that a tgtype can match more than one event, but only one level or timing. + */ +#define TRIGGER_TYPE_MATCHES(type, level, timing, event) \ + (((type) & (TRIGGER_TYPE_LEVEL_MASK | TRIGGER_TYPE_TIMING_MASK | (event))) == ((level) | (timing) | (event))) + #endif /* PG_TRIGGER_H */ diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 08bb22a3681..30dc314c00d 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -51,44 +51,48 @@ typedef struct TriggerData #define TRIGGER_EVENT_UPDATE 0x00000002 #define TRIGGER_EVENT_TRUNCATE 0x00000003 #define TRIGGER_EVENT_OPMASK 0x00000003 + #define TRIGGER_EVENT_ROW 0x00000004 + #define TRIGGER_EVENT_BEFORE 0x00000008 +#define TRIGGER_EVENT_AFTER 0x00000000 +#define TRIGGER_EVENT_INSTEAD 0x00000010 +#define TRIGGER_EVENT_TIMINGMASK 0x00000018 /* More TriggerEvent flags, used only within trigger.c */ -#define AFTER_TRIGGER_DEFERRABLE 0x00000010 -#define AFTER_TRIGGER_INITDEFERRED 0x00000020 +#define AFTER_TRIGGER_DEFERRABLE 0x00000020 +#define AFTER_TRIGGER_INITDEFERRED 0x00000040 -#define TRIGGER_FIRED_BY_INSERT(event) \ - (((TriggerEvent) (event) & TRIGGER_EVENT_OPMASK) == \ - TRIGGER_EVENT_INSERT) +#define TRIGGER_FIRED_BY_INSERT(event) \ + (((event) & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_INSERT) -#define TRIGGER_FIRED_BY_DELETE(event) \ - (((TriggerEvent) (event) & TRIGGER_EVENT_OPMASK) == \ - TRIGGER_EVENT_DELETE) +#define TRIGGER_FIRED_BY_DELETE(event) \ + (((event) & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_DELETE) -#define TRIGGER_FIRED_BY_UPDATE(event) \ - (((TriggerEvent) (event) & TRIGGER_EVENT_OPMASK) == \ - TRIGGER_EVENT_UPDATE) +#define TRIGGER_FIRED_BY_UPDATE(event) \ + (((event) & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE) #define TRIGGER_FIRED_BY_TRUNCATE(event) \ - (((TriggerEvent) (event) & TRIGGER_EVENT_OPMASK) == \ - TRIGGER_EVENT_TRUNCATE) + (((event) & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_TRUNCATE) + +#define TRIGGER_FIRED_FOR_ROW(event) \ + ((event) & TRIGGER_EVENT_ROW) -#define TRIGGER_FIRED_FOR_ROW(event) \ - ((TriggerEvent) (event) & TRIGGER_EVENT_ROW) +#define TRIGGER_FIRED_FOR_STATEMENT(event) \ + (!TRIGGER_FIRED_FOR_ROW(event)) -#define TRIGGER_FIRED_FOR_STATEMENT(event) \ - (!TRIGGER_FIRED_FOR_ROW (event)) +#define TRIGGER_FIRED_BEFORE(event) \ + (((event) & TRIGGER_EVENT_TIMINGMASK) == TRIGGER_EVENT_BEFORE) -#define TRIGGER_FIRED_BEFORE(event) \ - ((TriggerEvent) (event) & TRIGGER_EVENT_BEFORE) +#define TRIGGER_FIRED_AFTER(event) \ + (((event) & TRIGGER_EVENT_TIMINGMASK) == TRIGGER_EVENT_AFTER) -#define TRIGGER_FIRED_AFTER(event) \ - (!TRIGGER_FIRED_BEFORE (event)) +#define TRIGGER_FIRED_INSTEAD(event) \ + (((event) & TRIGGER_EVENT_TIMINGMASK) == TRIGGER_EVENT_INSTEAD) /* - * Definitions for the replication role based firing. + * Definitions for replication role based firing. */ #define SESSION_REPLICATION_ROLE_ORIGIN 0 #define SESSION_REPLICATION_ROLE_REPLICA 1 @@ -135,6 +139,9 @@ extern void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple, List *recheckIndexes); +extern HeapTuple ExecIRInsertTriggers(EState *estate, + ResultRelInfo *relinfo, + HeapTuple trigtuple); extern void ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASDeleteTriggers(EState *estate, @@ -146,6 +153,9 @@ extern bool ExecBRDeleteTriggers(EState *estate, extern void ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, ItemPointer tupleid); +extern bool ExecIRDeleteTriggers(EState *estate, + ResultRelInfo *relinfo, + HeapTuple trigtuple); extern void ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASUpdateTriggers(EState *estate, @@ -160,6 +170,10 @@ extern void ExecARUpdateTriggers(EState *estate, ItemPointer tupleid, HeapTuple newtuple, List *recheckIndexes); +extern HeapTuple ExecIRUpdateTriggers(EState *estate, + ResultRelInfo *relinfo, + HeapTuple oldtuple, + HeapTuple newtuple); extern void ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASTruncateTriggers(EState *estate, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b2f0fef5139..ca225d06ec1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1608,10 +1608,11 @@ typedef struct CreateTrigStmt RangeVar *relation; /* relation trigger is on */ List *funcname; /* qual. name of function to call */ List *args; /* list of (T_String) Values or NIL */ - bool before; /* BEFORE/AFTER */ bool row; /* ROW/STATEMENT */ + /* timing uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */ + int16 timing; /* BEFORE, AFTER, or INSTEAD */ /* events uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */ - int16 events; /* INSERT/UPDATE/DELETE/TRUNCATE */ + int16 events; /* "OR" of INSERT/UPDATE/DELETE/TRUNCATE */ List *columns; /* column names, or NIL for all columns */ Node *whenClause; /* qual expression, or NULL if none */ bool isconstraint; /* This is a constraint trigger */ diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 17ad88820d8..9ad92c299e8 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -71,26 +71,31 @@ typedef struct Trigger typedef struct TriggerDesc { + Trigger *triggers; /* array of Trigger structs */ + int numtriggers; /* number of array entries */ + /* - * Index data to identify which triggers are which. Since each trigger - * can appear in more than one class, for each class we provide a list of - * integer indexes into the triggers array. The class codes are defined - * by TRIGGER_EVENT_xxx macros in commands/trigger.h. + * These flags indicate whether the array contains at least one of each + * type of trigger. We use these to skip searching the array if not. */ -#define TRIGGER_NUM_EVENT_CLASSES 4 - - uint16 n_before_statement[TRIGGER_NUM_EVENT_CLASSES]; - uint16 n_before_row[TRIGGER_NUM_EVENT_CLASSES]; - uint16 n_after_row[TRIGGER_NUM_EVENT_CLASSES]; - uint16 n_after_statement[TRIGGER_NUM_EVENT_CLASSES]; - int *tg_before_statement[TRIGGER_NUM_EVENT_CLASSES]; - int *tg_before_row[TRIGGER_NUM_EVENT_CLASSES]; - int *tg_after_row[TRIGGER_NUM_EVENT_CLASSES]; - int *tg_after_statement[TRIGGER_NUM_EVENT_CLASSES]; - - /* The actual array of triggers is here */ - Trigger *triggers; - int numtriggers; + bool trig_insert_before_row; + bool trig_insert_after_row; + bool trig_insert_instead_row; + bool trig_insert_before_statement; + bool trig_insert_after_statement; + bool trig_update_before_row; + bool trig_update_after_row; + bool trig_update_instead_row; + bool trig_update_before_statement; + bool trig_update_after_statement; + bool trig_delete_before_row; + bool trig_delete_after_row; + bool trig_delete_instead_row; + bool trig_delete_before_statement; + bool trig_delete_after_statement; + /* there are no row-level truncate triggers */ + bool trig_truncate_before_statement; + bool trig_truncate_after_statement; } TriggerDesc; diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out index b5af566b27a..8cd74cb4b99 100644 --- a/src/pl/plperl/expected/plperl_trigger.out +++ b/src/pl/plperl/expected/plperl_trigger.out @@ -124,6 +124,84 @@ NOTICE: $_TD->{when} = 'BEFORE' CONTEXT: PL/Perl function "trigger_data" DROP TRIGGER show_trigger_data_trig on trigger_test; +insert into trigger_test values(1,'insert'); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; +CREATE TRIGGER show_trigger_data_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); +insert into trigger_test_view values(2,'insert'); +NOTICE: $_TD->{argc} = '2' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{args} = ['24', 'skidoo view'] +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{event} = 'INSERT' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{level} = 'ROW' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{name} = 'show_trigger_data_trig' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{new} = {'i' => '2', 'v' => 'insert'} +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{relid} = 'bogus:12345' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{relname} = 'trigger_test_view' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{table_name} = 'trigger_test_view' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{table_schema} = 'public' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{when} = 'INSTEAD OF' +CONTEXT: PL/Perl function "trigger_data" +update trigger_test_view set v = 'update' where i = 1; +NOTICE: $_TD->{argc} = '2' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{args} = ['24', 'skidoo view'] +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{event} = 'UPDATE' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{level} = 'ROW' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{name} = 'show_trigger_data_trig' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{new} = {'i' => '1', 'v' => 'update'} +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{old} = {'i' => '1', 'v' => 'insert'} +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{relid} = 'bogus:12345' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{relname} = 'trigger_test_view' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{table_name} = 'trigger_test_view' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{table_schema} = 'public' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{when} = 'INSTEAD OF' +CONTEXT: PL/Perl function "trigger_data" +delete from trigger_test_view; +NOTICE: $_TD->{argc} = '2' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{args} = ['24', 'skidoo view'] +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{event} = 'DELETE' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{level} = 'ROW' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{name} = 'show_trigger_data_trig' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{old} = {'i' => '1', 'v' => 'insert'} +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{relid} = 'bogus:12345' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{relname} = 'trigger_test_view' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{table_name} = 'trigger_test_view' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{table_schema} = 'public' +CONTEXT: PL/Perl function "trigger_data" +NOTICE: $_TD->{when} = 'INSTEAD OF' +CONTEXT: PL/Perl function "trigger_data" +DROP VIEW trigger_test_view; +delete from trigger_test; DROP FUNCTION trigger_data(); CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$ diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c index b4ced1ce8d4..d3e746e6e89 100644 --- a/src/pl/plperl/plperl.c +++ b/src/pl/plperl/plperl.c @@ -1087,6 +1087,8 @@ plperl_trigger_build_args(FunctionCallInfo fcinfo) when = "BEFORE"; else if (TRIGGER_FIRED_AFTER(tdata->tg_event)) when = "AFTER"; + else if (TRIGGER_FIRED_INSTEAD(tdata->tg_event)) + when = "INSTEAD OF"; else when = "UNKNOWN"; hv_store_string(hv, "when", newSVstring(when)); diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plperl_trigger.sql index d55f79aa7fb..1e68759b0f8 100644 --- a/src/pl/plperl/sql/plperl_trigger.sql +++ b/src/pl/plperl/sql/plperl_trigger.sql @@ -60,6 +60,20 @@ update trigger_test set v = 'update' where i = 1; delete from trigger_test; DROP TRIGGER show_trigger_data_trig on trigger_test; + +insert into trigger_test values(1,'insert'); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; + +CREATE TRIGGER show_trigger_data_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); + +insert into trigger_test_view values(2,'insert'); +update trigger_test_view set v = 'update' where i = 1; +delete from trigger_test_view; + +DROP VIEW trigger_test_view; +delete from trigger_test; DROP FUNCTION trigger_data(); diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index e2b5f77c9d9..e3f85729257 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -581,8 +581,10 @@ plpgsql_exec_trigger(PLpgSQL_function *func, var->value = CStringGetTextDatum("BEFORE"); else if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) var->value = CStringGetTextDatum("AFTER"); + else if (TRIGGER_FIRED_INSTEAD(trigdata->tg_event)) + var->value = CStringGetTextDatum("INSTEAD OF"); else - elog(ERROR, "unrecognized trigger execution time: not BEFORE or AFTER"); + elog(ERROR, "unrecognized trigger execution time: not BEFORE, AFTER, or INSTEAD OF"); var->isnull = false; var->freeval = true; diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out index 3192ff1d48e..275d0f74399 100644 --- a/src/pl/plpython/expected/plpython_trigger.out +++ b/src/pl/plpython/expected/plpython_trigger.out @@ -294,11 +294,81 @@ NOTICE: TD[table_schema] => public CONTEXT: PL/Python function "trigger_data" NOTICE: TD[when] => BEFORE CONTEXT: PL/Python function "trigger_data" +DROP TRIGGER show_trigger_data_trig_stmt on trigger_test; +DROP TRIGGER show_trigger_data_trig_before on trigger_test; +DROP TRIGGER show_trigger_data_trig_after on trigger_test; +insert into trigger_test values(1,'insert'); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; +CREATE TRIGGER show_trigger_data_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); +insert into trigger_test_view values(2,'insert'); +NOTICE: TD[args] => ['24', 'skidoo view'] +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[event] => INSERT +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[level] => ROW +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[name] => show_trigger_data_trig +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[new] => {'i': 2, 'v': 'insert'} +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[old] => None +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[relid] => bogus:12345 +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[table_name] => trigger_test_view +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[table_schema] => public +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[when] => INSTEAD OF +CONTEXT: PL/Python function "trigger_data" +update trigger_test_view set v = 'update' where i = 1; +NOTICE: TD[args] => ['24', 'skidoo view'] +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[event] => UPDATE +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[level] => ROW +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[name] => show_trigger_data_trig +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[new] => {'i': 1, 'v': 'update'} +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[old] => {'i': 1, 'v': 'insert'} +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[relid] => bogus:12345 +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[table_name] => trigger_test_view +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[table_schema] => public +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[when] => INSTEAD OF +CONTEXT: PL/Python function "trigger_data" +delete from trigger_test_view; +NOTICE: TD[args] => ['24', 'skidoo view'] +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[event] => DELETE +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[level] => ROW +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[name] => show_trigger_data_trig +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[new] => None +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[old] => {'i': 1, 'v': 'insert'} +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[relid] => bogus:12345 +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[table_name] => trigger_test_view +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[table_schema] => public +CONTEXT: PL/Python function "trigger_data" +NOTICE: TD[when] => INSTEAD OF +CONTEXT: PL/Python function "trigger_data" DROP FUNCTION trigger_data() CASCADE; -NOTICE: drop cascades to 3 other objects -DETAIL: drop cascades to trigger show_trigger_data_trig_before on table trigger_test -drop cascades to trigger show_trigger_data_trig_after on table trigger_test -drop cascades to trigger show_trigger_data_trig_stmt on table trigger_test +NOTICE: drop cascades to trigger show_trigger_data_trig on view trigger_test_view +DROP VIEW trigger_test_view; +delete from trigger_test; -- -- trigger error handling -- diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c index 4c53987fa4a..c468cf00d07 100644 --- a/src/pl/plpython/plpython.c +++ b/src/pl/plpython/plpython.c @@ -845,6 +845,8 @@ PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *proc, HeapTuple *r pltwhen = PyString_FromString("BEFORE"); else if (TRIGGER_FIRED_AFTER(tdata->tg_event)) pltwhen = PyString_FromString("AFTER"); + else if (TRIGGER_FIRED_INSTEAD(tdata->tg_event)) + pltwhen = PyString_FromString("INSTEAD OF"); else { elog(ERROR, "unrecognized WHEN tg_event: %u", tdata->tg_event); diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql index c60a6737801..c3af5c211e6 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -99,7 +99,24 @@ update trigger_test set v = 'update' where i = 1; delete from trigger_test; truncate table trigger_test; +DROP TRIGGER show_trigger_data_trig_stmt on trigger_test; +DROP TRIGGER show_trigger_data_trig_before on trigger_test; +DROP TRIGGER show_trigger_data_trig_after on trigger_test; + +insert into trigger_test values(1,'insert'); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; + +CREATE TRIGGER show_trigger_data_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); + +insert into trigger_test_view values(2,'insert'); +update trigger_test_view set v = 'update' where i = 1; +delete from trigger_test_view; + DROP FUNCTION trigger_data() CASCADE; +DROP VIEW trigger_test_view; +delete from trigger_test; -- diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out index cdab74a436f..b585736f711 100644 --- a/src/pl/tcl/expected/pltcl_queries.out +++ b/src/pl/tcl/expected/pltcl_queries.out @@ -196,6 +196,42 @@ NOTICE: TG_table_name: trigger_test NOTICE: TG_table_schema: public NOTICE: TG_when: BEFORE NOTICE: args: {23 skidoo} +insert into trigger_test_view values(2,'insert'); +NOTICE: NEW: {i: 2, v: insert} +NOTICE: OLD: {} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_view_trig +NOTICE: TG_op: INSERT +NOTICE: TG_relatts: {{} i v} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_view +NOTICE: TG_table_schema: public +NOTICE: TG_when: {INSTEAD OF} +NOTICE: args: {24 {skidoo view}} +update trigger_test_view set v = 'update' where i=1; +NOTICE: NEW: {i: 1, v: update} +NOTICE: OLD: {i: 1, v: insert} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_view_trig +NOTICE: TG_op: UPDATE +NOTICE: TG_relatts: {{} i v} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_view +NOTICE: TG_table_schema: public +NOTICE: TG_when: {INSTEAD OF} +NOTICE: args: {24 {skidoo view}} +delete from trigger_test_view; +NOTICE: NEW: {} +NOTICE: OLD: {i: 1, v: insert} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_view_trig +NOTICE: TG_op: DELETE +NOTICE: TG_relatts: {{} i v} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_view +NOTICE: TG_table_schema: public +NOTICE: TG_when: {INSTEAD OF} +NOTICE: args: {24 {skidoo view}} update trigger_test set v = 'update' where i = 1; NOTICE: NEW: {i: 1, v: update} NOTICE: OLD: {i: 1, v: insert} diff --git a/src/pl/tcl/expected/pltcl_queries_1.out b/src/pl/tcl/expected/pltcl_queries_1.out index 5788df79df4..e3fd24d6b10 100644 --- a/src/pl/tcl/expected/pltcl_queries_1.out +++ b/src/pl/tcl/expected/pltcl_queries_1.out @@ -196,6 +196,42 @@ NOTICE: TG_table_name: trigger_test NOTICE: TG_table_schema: public NOTICE: TG_when: BEFORE NOTICE: args: {23 skidoo} +insert into trigger_test_view values(2,'insert'); +NOTICE: NEW: {i: 2, v: insert} +NOTICE: OLD: {} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_view_trig +NOTICE: TG_op: INSERT +NOTICE: TG_relatts: {{} i v} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_view +NOTICE: TG_table_schema: public +NOTICE: TG_when: {INSTEAD OF} +NOTICE: args: {24 {skidoo view}} +update trigger_test_view set v = 'update' where i=1; +NOTICE: NEW: {i: 1, v: update} +NOTICE: OLD: {i: 1, v: insert} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_view_trig +NOTICE: TG_op: UPDATE +NOTICE: TG_relatts: {{} i v} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_view +NOTICE: TG_table_schema: public +NOTICE: TG_when: {INSTEAD OF} +NOTICE: args: {24 {skidoo view}} +delete from trigger_test_view; +NOTICE: NEW: {} +NOTICE: OLD: {i: 1, v: insert} +NOTICE: TG_level: ROW +NOTICE: TG_name: show_trigger_data_view_trig +NOTICE: TG_op: DELETE +NOTICE: TG_relatts: {{} i v} +NOTICE: TG_relid: bogus:12345 +NOTICE: TG_table_name: trigger_test_view +NOTICE: TG_table_schema: public +NOTICE: TG_when: {INSTEAD OF} +NOTICE: args: {24 {skidoo view}} update trigger_test set v = 'update' where i = 1; NOTICE: NEW: {i: 1, v: update} NOTICE: OLD: {i: 1, v: insert} diff --git a/src/pl/tcl/expected/pltcl_setup.out b/src/pl/tcl/expected/pltcl_setup.out index e46c1c3e987..f577e662771 100644 --- a/src/pl/tcl/expected/pltcl_setup.out +++ b/src/pl/tcl/expected/pltcl_setup.out @@ -51,6 +51,7 @@ create function check_pkey1_exists(int4, bpchar) returns bool as E' -- dump trigger data CREATE TABLE trigger_test (i int, v text ); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$ if { [info exists TG_relid] } { @@ -85,6 +86,9 @@ $_$; CREATE TRIGGER show_trigger_data_trig BEFORE INSERT OR UPDATE OR DELETE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +CREATE TRIGGER show_trigger_data_view_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); -- -- Trigger function on every change to T_pkey1 -- diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index 1c45751d8b3..2f0108c3d4c 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -889,6 +889,8 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, bool pltrusted) Tcl_DStringAppendElement(&tcl_cmd, "BEFORE"); else if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) Tcl_DStringAppendElement(&tcl_cmd, "AFTER"); + else if (TRIGGER_FIRED_INSTEAD(trigdata->tg_event)) + Tcl_DStringAppendElement(&tcl_cmd, "INSTEAD OF"); else elog(ERROR, "unrecognized WHEN tg_event: %u", trigdata->tg_event); diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql index 3a99132ae4d..ee711d5170a 100644 --- a/src/pl/tcl/sql/pltcl_queries.sql +++ b/src/pl/tcl/sql/pltcl_queries.sql @@ -73,8 +73,12 @@ select 100 @< 4; select * from T_pkey1 order by key1 using @<, key2; select * from T_pkey2 order by key1 using @<, key2; - -- show dump of trigger data insert into trigger_test values(1,'insert'); + +insert into trigger_test_view values(2,'insert'); +update trigger_test_view set v = 'update' where i=1; +delete from trigger_test_view; + update trigger_test set v = 'update' where i = 1; delete from trigger_test; diff --git a/src/pl/tcl/sql/pltcl_setup.sql b/src/pl/tcl/sql/pltcl_setup.sql index 4a581ed8b12..a9370d258dc 100644 --- a/src/pl/tcl/sql/pltcl_setup.sql +++ b/src/pl/tcl/sql/pltcl_setup.sql @@ -60,6 +60,8 @@ create function check_pkey1_exists(int4, bpchar) returns bool as E' CREATE TABLE trigger_test (i int, v text ); +CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test; + CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$ if { [info exists TG_relid] } { @@ -96,6 +98,9 @@ CREATE TRIGGER show_trigger_data_trig BEFORE INSERT OR UPDATE OR DELETE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +CREATE TRIGGER show_trigger_data_view_trig +INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view +FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view'); -- -- Trigger function on every change to T_pkey1 diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index fe980795420..1b9cdd43753 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -791,3 +791,628 @@ SELECT * FROM min_updates_test_oids; DROP TABLE min_updates_test; DROP TABLE min_updates_test_oids; +-- +-- Test triggers on views +-- +CREATE VIEW main_view AS SELECT a, b FROM main_table; +-- Updates should fail without rules or triggers +INSERT INTO main_view VALUES (1,2); +ERROR: cannot insert into view "main_view" +HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger. +UPDATE main_view SET b = 20 WHERE a = 50; +ERROR: cannot update view "main_view" +HINT: You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger. +DELETE FROM main_view WHERE a = 50; +ERROR: cannot delete from view "main_view" +HINT: You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger. +-- Should fail even when there are no matching rows +DELETE FROM main_view WHERE a = 51; +ERROR: cannot delete from view "main_view" +HINT: You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger. +-- VIEW trigger function +CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger +LANGUAGE plpgsql AS $$ +declare + argstr text := ''; +begin + for i in 0 .. TG_nargs - 1 loop + if i > 0 then + argstr := argstr || ', '; + end if; + argstr := argstr || TG_argv[i]; + end loop; + + raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr; + + if TG_LEVEL = 'ROW' then + if TG_OP = 'INSERT' then + raise NOTICE 'NEW: %', NEW; + INSERT INTO main_table VALUES (NEW.a, NEW.b); + RETURN NEW; + end if; + + if TG_OP = 'UPDATE' then + raise NOTICE 'OLD: %, NEW: %', OLD, NEW; + UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b; + if NOT FOUND then RETURN NULL; end if; + RETURN NEW; + end if; + + if TG_OP = 'DELETE' then + raise NOTICE 'OLD: %', OLD; + DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b; + if NOT FOUND then RETURN NULL; end if; + RETURN OLD; + end if; + end if; + + RETURN NULL; +end; +$$; +-- Before row triggers aren't allowed on views +CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +-- After row triggers aren't allowed on views +CREATE TRIGGER invalid_trig AFTER INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +CREATE TRIGGER invalid_trig AFTER DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have row-level BEFORE or AFTER triggers. +-- Truncate triggers aren't allowed on views +CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view +EXECUTE PROCEDURE trigger_func('before_tru_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have TRUNCATE triggers. +CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view +EXECUTE PROCEDURE trigger_func('before_tru_row'); +ERROR: "main_view" is a view +DETAIL: Views cannot have TRUNCATE triggers. +-- INSTEAD OF triggers aren't allowed on tables +CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); +ERROR: "main_table" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: "main_table" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); +ERROR: "main_table" is a table +DETAIL: Tables cannot have INSTEAD OF triggers. +-- Don't support WHEN clauses with INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view +FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: INSTEAD OF triggers cannot have WHEN conditions +-- Don't support column-level INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: INSTEAD OF triggers cannot have column lists +-- Don't support statement-level INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view +EXECUTE PROCEDURE view_trigger('instead_of_upd'); +ERROR: INSTEAD OF triggers must be FOR EACH ROW +-- Valid INSTEAD OF triggers +CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); +CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); +CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); +-- Valid BEFORE statement VIEW triggers +CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt'); +CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt'); +CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt'); +-- Valid AFTER statement VIEW triggers +CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt'); +CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt'); +CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt'); +\set QUIET false +-- Insert into view using trigger +INSERT INTO main_view VALUES (20, 30); +NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) +NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) +NOTICE: NEW: (20,30) +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)" +PL/pgSQL function "view_trigger" line 17 at SQL statement +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)" +PL/pgSQL function "view_trigger" line 17 at SQL statement +NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) +INSERT 0 1 +INSERT INTO main_view VALUES (21, 31) RETURNING a, b; +NOTICE: main_view BEFORE INSERT STATEMENT (before_view_ins_stmt) +NOTICE: main_view INSTEAD OF INSERT ROW (instead_of_ins) +NOTICE: NEW: (21,31) +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)" +PL/pgSQL function "view_trigger" line 17 at SQL statement +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)" +PL/pgSQL function "view_trigger" line 17 at SQL statement +NOTICE: main_view AFTER INSERT STATEMENT (after_view_ins_stmt) + a | b +----+---- + 21 | 31 +(1 row) + +INSERT 0 1 +-- Table trigger will prevent updates +UPDATE main_view SET b = 31 WHERE a = 20; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (20,30), NEW: (20,31) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) +UPDATE 0 +UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (21,31), NEW: (21,32) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) + a | b +---+--- +(0 rows) + +UPDATE 0 +-- Remove table trigger to allow updates +DROP TRIGGER before_upd_a_row_trig ON main_table; +DROP TRIGGER +UPDATE main_view SET b = 31 WHERE a = 20; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (20,30), NEW: (20,31) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) +UPDATE 1 +UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view INSTEAD OF UPDATE ROW (instead_of_upd) +NOTICE: OLD: (21,31), NEW: (21,32) +NOTICE: trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +CONTEXT: SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b" +PL/pgSQL function "view_trigger" line 23 at SQL statement +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) + a | b +----+---- + 21 | 32 +(1 row) + +UPDATE 1 +-- Before and after stmt triggers should fire even when no rows are affected +UPDATE main_view SET b = 0 WHERE false; +NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt) +NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt) +UPDATE 0 +-- Delete from view using trigger +DELETE FROM main_view WHERE a IN (20,21); +NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (21,10) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (20,31) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (21,32) +NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) +DELETE 3 +DELETE FROM main_view WHERE a = 31 RETURNING a, b; +NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt) +NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del) +NOTICE: OLD: (31,10) +NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt) + a | b +----+---- + 31 | 10 +(1 row) + +DELETE 1 +\set QUIET true +-- Describe view should list triggers +\d main_view + View "public.main_view" + Column | Type | Modifiers +--------+---------+----------- + a | integer | + b | integer | +Triggers: + after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt') + after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt') + after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt') + before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt') + before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt') + before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt') + instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del') + instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins') + instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd') + +-- Test dropping view triggers +DROP TRIGGER instead_of_insert_trig ON main_view; +DROP TRIGGER instead_of_delete_trig ON main_view; +\d+ main_view + View "public.main_view" + Column | Type | Modifiers | Storage | Description +--------+---------+-----------+---------+------------- + a | integer | | plain | + b | integer | | plain | +View definition: + SELECT main_table.a, main_table.b + FROM main_table; +Triggers: + after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt') + after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt') + after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt') + before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt') + before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt') + before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt') + instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd') + +DROP VIEW main_view; +-- +-- Test triggers on a join view +-- +CREATE TABLE country_table ( + country_id serial primary key, + country_name text unique not null, + continent text not null +); +NOTICE: CREATE TABLE will create implicit sequence "country_table_country_id_seq" for serial column "country_table.country_id" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "country_table_pkey" for table "country_table" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "country_table_country_name_key" for table "country_table" +INSERT INTO country_table (country_name, continent) + VALUES ('Japan', 'Asia'), + ('UK', 'Europe'), + ('USA', 'North America') + RETURNING *; + country_id | country_name | continent +------------+--------------+--------------- + 1 | Japan | Asia + 2 | UK | Europe + 3 | USA | North America +(3 rows) + +CREATE TABLE city_table ( + city_id serial primary key, + city_name text not null, + population bigint, + country_id int references country_table +); +NOTICE: CREATE TABLE will create implicit sequence "city_table_city_id_seq" for serial column "city_table.city_id" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "city_table_pkey" for table "city_table" +CREATE VIEW city_view AS + SELECT city_id, city_name, population, country_name, continent + FROM city_table ci + LEFT JOIN country_table co ON co.country_id = ci.country_id; +CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$ +declare + ctry_id int; +begin + if NEW.country_name IS NOT NULL then + SELECT country_id, continent INTO ctry_id, NEW.continent + FROM country_table WHERE country_name = NEW.country_name; + if NOT FOUND then + raise exception 'No such country: "%"', NEW.country_name; + end if; + else + NEW.continent := NULL; + end if; + + if NEW.city_id IS NOT NULL then + INSERT INTO city_table + VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id); + else + INSERT INTO city_table(city_name, population, country_id) + VALUES(NEW.city_name, NEW.population, ctry_id) + RETURNING city_id INTO NEW.city_id; + end if; + + RETURN NEW; +end; +$$; +CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_insert(); +CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$ +begin + DELETE FROM city_table WHERE city_id = OLD.city_id; + if NOT FOUND then RETURN NULL; end if; + RETURN OLD; +end; +$$; +CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_delete(); +CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$ +declare + ctry_id int; +begin + if NEW.country_name IS DISTINCT FROM OLD.country_name then + SELECT country_id, continent INTO ctry_id, NEW.continent + FROM country_table WHERE country_name = NEW.country_name; + if NOT FOUND then + raise exception 'No such country: "%"', NEW.country_name; + end if; + + UPDATE city_table SET city_name = NEW.city_name, + population = NEW.population, + country_id = ctry_id + WHERE city_id = OLD.city_id; + else + UPDATE city_table SET city_name = NEW.city_name, + population = NEW.population + WHERE city_id = OLD.city_id; + NEW.continent := OLD.continent; + end if; + + if NOT FOUND then RETURN NULL; end if; + RETURN NEW; +end; +$$; +CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_update(); +\set QUIET false +-- INSERT .. RETURNING +INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 1 | Tokyo | | | +(1 row) + +INSERT 0 1 +INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 2 | London | 7556900 | | +(1 row) + +INSERT 0 1 +INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *; + city_id | city_name | population | country_name | continent +---------+---------------+------------+--------------+--------------- + 3 | Washington DC | | USA | North America +(1 row) + +INSERT 0 1 +INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 123456 | New York | | | +(1 row) + +INSERT 0 1 +INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *; + city_id | city_name | population | country_name | continent +---------+------------+------------+--------------+----------- + 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +INSERT 0 1 +-- UPDATE .. RETURNING +UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error +ERROR: No such country: "Japon" +UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match +UPDATE 0 +UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 1 | Tokyo | | Japan | Asia +(1 row) + +UPDATE 1 +UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 1 | Tokyo | 13010279 | Japan | Asia +(1 row) + +UPDATE 1 +UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 123456 | New York | | UK | Europe +(1 row) + +UPDATE 1 +UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+--------------- + 123456 | New York | 8391881 | USA | North America +(1 row) + +UPDATE 1 +UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *; + city_id | city_name | population | country_name | continent +---------+------------+------------+--------------+----------- + 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +UPDATE 1 +UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2 + WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *; + city_id | city_name | population | country_name | continent | city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+----------- + 2 | London | 7556900 | UK | Europe | 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +UPDATE 1 +-- DELETE .. RETURNING +DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *; + city_id | city_name | population | country_name | continent +---------+------------+------------+--------------+----------- + 234567 | Birmingham | 1016800 | UK | Europe +(1 row) + +DELETE 1 +\set QUIET true +-- read-only view with WHERE clause +CREATE VIEW european_city_view AS + SELECT * FROM city_view WHERE continent = 'Europe'; +SELECT count(*) FROM european_city_view; + count +------- + 1 +(1 row) + +CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql +AS 'begin RETURN NULL; end'; +CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE +ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn(); +\set QUIET false +INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z'); +INSERT 0 0 +UPDATE european_city_view SET population = 10000; +UPDATE 0 +DELETE FROM european_city_view; +DELETE 0 +\set QUIET true +-- rules bypassing no-op triggers +CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view +DO INSTEAD INSERT INTO city_view +VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent) +RETURNING *; +CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view +DO INSTEAD UPDATE city_view SET + city_name = NEW.city_name, + population = NEW.population, + country_name = NEW.country_name +WHERE city_id = OLD.city_id +RETURNING NEW.*; +CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view +DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *; +\set QUIET false +-- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are +INSERT INTO european_city_view(city_name, country_name) + VALUES ('Cambridge', 'USA') RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+--------------- + 4 | Cambridge | | USA | North America +(1 row) + +INSERT 0 1 +UPDATE european_city_view SET country_name = 'UK' + WHERE city_name = 'Cambridge'; +UPDATE 0 +DELETE FROM european_city_view WHERE city_name = 'Cambridge'; +DELETE 0 +-- UPDATE and DELETE via rule and trigger +UPDATE city_view SET country_name = 'UK' + WHERE city_name = 'Cambridge' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 4 | Cambridge | | UK | Europe +(1 row) + +UPDATE 1 +UPDATE european_city_view SET population = 122800 + WHERE city_name = 'Cambridge' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 4 | Cambridge | 122800 | UK | Europe +(1 row) + +UPDATE 1 +DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *; + city_id | city_name | population | country_name | continent +---------+-----------+------------+--------------+----------- + 4 | Cambridge | 122800 | UK | Europe +(1 row) + +DELETE 1 +-- join UPDATE test +UPDATE city_view v SET population = 599657 + FROM city_table ci, country_table co + WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA' + AND v.city_id = ci.city_id AND v.country_name = co.country_name + RETURNING co.country_id, v.country_name, + v.city_id, v.city_name, v.population; + country_id | country_name | city_id | city_name | population +------------+--------------+---------+---------------+------------ + 3 | USA | 3 | Washington DC | 599657 +(1 row) + +UPDATE 1 +\set QUIET true +SELECT * FROM city_view; + city_id | city_name | population | country_name | continent +---------+---------------+------------+--------------+--------------- + 1 | Tokyo | 13010279 | Japan | Asia + 123456 | New York | 8391881 | USA | North America + 2 | London | 7556900 | UK | Europe + 3 | Washington DC | 599657 | USA | North America +(4 rows) + +DROP TABLE city_table CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to view city_view +drop cascades to view european_city_view +DROP TABLE country_table; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 68495ca112e..a830b3b392b 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -578,3 +578,360 @@ SELECT * FROM min_updates_test_oids; DROP TABLE min_updates_test; DROP TABLE min_updates_test_oids; + +-- +-- Test triggers on views +-- + +CREATE VIEW main_view AS SELECT a, b FROM main_table; + +-- Updates should fail without rules or triggers +INSERT INTO main_view VALUES (1,2); +UPDATE main_view SET b = 20 WHERE a = 50; +DELETE FROM main_view WHERE a = 50; +-- Should fail even when there are no matching rows +DELETE FROM main_view WHERE a = 51; + +-- VIEW trigger function +CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger +LANGUAGE plpgsql AS $$ +declare + argstr text := ''; +begin + for i in 0 .. TG_nargs - 1 loop + if i > 0 then + argstr := argstr || ', '; + end if; + argstr := argstr || TG_argv[i]; + end loop; + + raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr; + + if TG_LEVEL = 'ROW' then + if TG_OP = 'INSERT' then + raise NOTICE 'NEW: %', NEW; + INSERT INTO main_table VALUES (NEW.a, NEW.b); + RETURN NEW; + end if; + + if TG_OP = 'UPDATE' then + raise NOTICE 'OLD: %, NEW: %', OLD, NEW; + UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b; + if NOT FOUND then RETURN NULL; end if; + RETURN NEW; + end if; + + if TG_OP = 'DELETE' then + raise NOTICE 'OLD: %', OLD; + DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b; + if NOT FOUND then RETURN NULL; end if; + RETURN OLD; + end if; + end if; + + RETURN NULL; +end; +$$; + +-- Before row triggers aren't allowed on views +CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); + +CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); + +CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); + +-- After row triggers aren't allowed on views +CREATE TRIGGER invalid_trig AFTER INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row'); + +CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row'); + +CREATE TRIGGER invalid_trig AFTER DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row'); + +-- Truncate triggers aren't allowed on views +CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view +EXECUTE PROCEDURE trigger_func('before_tru_row'); + +CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view +EXECUTE PROCEDURE trigger_func('before_tru_row'); + +-- INSTEAD OF triggers aren't allowed on tables +CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); + +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); + +CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); + +-- Don't support WHEN clauses with INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view +FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd'); + +-- Don't support column-level INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); + +-- Don't support statement-level INSTEAD OF triggers +CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view +EXECUTE PROCEDURE view_trigger('instead_of_upd'); + +-- Valid INSTEAD OF triggers +CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins'); + +CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd'); + +CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view +FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del'); + +-- Valid BEFORE statement VIEW triggers +CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt'); + +CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt'); + +CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt'); + +-- Valid AFTER statement VIEW triggers +CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt'); + +CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt'); + +CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view +FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt'); + +\set QUIET false + +-- Insert into view using trigger +INSERT INTO main_view VALUES (20, 30); +INSERT INTO main_view VALUES (21, 31) RETURNING a, b; + +-- Table trigger will prevent updates +UPDATE main_view SET b = 31 WHERE a = 20; +UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; + +-- Remove table trigger to allow updates +DROP TRIGGER before_upd_a_row_trig ON main_table; +UPDATE main_view SET b = 31 WHERE a = 20; +UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b; + +-- Before and after stmt triggers should fire even when no rows are affected +UPDATE main_view SET b = 0 WHERE false; + +-- Delete from view using trigger +DELETE FROM main_view WHERE a IN (20,21); +DELETE FROM main_view WHERE a = 31 RETURNING a, b; + +\set QUIET true + +-- Describe view should list triggers +\d main_view + +-- Test dropping view triggers +DROP TRIGGER instead_of_insert_trig ON main_view; +DROP TRIGGER instead_of_delete_trig ON main_view; +\d+ main_view +DROP VIEW main_view; + +-- +-- Test triggers on a join view +-- +CREATE TABLE country_table ( + country_id serial primary key, + country_name text unique not null, + continent text not null +); + +INSERT INTO country_table (country_name, continent) + VALUES ('Japan', 'Asia'), + ('UK', 'Europe'), + ('USA', 'North America') + RETURNING *; + +CREATE TABLE city_table ( + city_id serial primary key, + city_name text not null, + population bigint, + country_id int references country_table +); + +CREATE VIEW city_view AS + SELECT city_id, city_name, population, country_name, continent + FROM city_table ci + LEFT JOIN country_table co ON co.country_id = ci.country_id; + +CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$ +declare + ctry_id int; +begin + if NEW.country_name IS NOT NULL then + SELECT country_id, continent INTO ctry_id, NEW.continent + FROM country_table WHERE country_name = NEW.country_name; + if NOT FOUND then + raise exception 'No such country: "%"', NEW.country_name; + end if; + else + NEW.continent := NULL; + end if; + + if NEW.city_id IS NOT NULL then + INSERT INTO city_table + VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id); + else + INSERT INTO city_table(city_name, population, country_id) + VALUES(NEW.city_name, NEW.population, ctry_id) + RETURNING city_id INTO NEW.city_id; + end if; + + RETURN NEW; +end; +$$; + +CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_insert(); + +CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$ +begin + DELETE FROM city_table WHERE city_id = OLD.city_id; + if NOT FOUND then RETURN NULL; end if; + RETURN OLD; +end; +$$; + +CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_delete(); + +CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$ +declare + ctry_id int; +begin + if NEW.country_name IS DISTINCT FROM OLD.country_name then + SELECT country_id, continent INTO ctry_id, NEW.continent + FROM country_table WHERE country_name = NEW.country_name; + if NOT FOUND then + raise exception 'No such country: "%"', NEW.country_name; + end if; + + UPDATE city_table SET city_name = NEW.city_name, + population = NEW.population, + country_id = ctry_id + WHERE city_id = OLD.city_id; + else + UPDATE city_table SET city_name = NEW.city_name, + population = NEW.population + WHERE city_id = OLD.city_id; + NEW.continent := OLD.continent; + end if; + + if NOT FOUND then RETURN NULL; end if; + RETURN NEW; +end; +$$; + +CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view +FOR EACH ROW EXECUTE PROCEDURE city_update(); + +\set QUIET false + +-- INSERT .. RETURNING +INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *; +INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *; +INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *; +INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *; +INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *; + +-- UPDATE .. RETURNING +UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error +UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match +UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK + +UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *; +UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *; +UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *; +UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *; +UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2 + WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *; + +-- DELETE .. RETURNING +DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *; + +\set QUIET true + +-- read-only view with WHERE clause +CREATE VIEW european_city_view AS + SELECT * FROM city_view WHERE continent = 'Europe'; +SELECT count(*) FROM european_city_view; + +CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql +AS 'begin RETURN NULL; end'; + +CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE +ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn(); + +\set QUIET false + +INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z'); +UPDATE european_city_view SET population = 10000; +DELETE FROM european_city_view; + +\set QUIET true + +-- rules bypassing no-op triggers +CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view +DO INSTEAD INSERT INTO city_view +VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent) +RETURNING *; + +CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view +DO INSTEAD UPDATE city_view SET + city_name = NEW.city_name, + population = NEW.population, + country_name = NEW.country_name +WHERE city_id = OLD.city_id +RETURNING NEW.*; + +CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view +DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *; + +\set QUIET false + +-- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are +INSERT INTO european_city_view(city_name, country_name) + VALUES ('Cambridge', 'USA') RETURNING *; +UPDATE european_city_view SET country_name = 'UK' + WHERE city_name = 'Cambridge'; +DELETE FROM european_city_view WHERE city_name = 'Cambridge'; + +-- UPDATE and DELETE via rule and trigger +UPDATE city_view SET country_name = 'UK' + WHERE city_name = 'Cambridge' RETURNING *; +UPDATE european_city_view SET population = 122800 + WHERE city_name = 'Cambridge' RETURNING *; +DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *; + +-- join UPDATE test +UPDATE city_view v SET population = 599657 + FROM city_table ci, country_table co + WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA' + AND v.city_id = ci.city_id AND v.country_name = co.country_name + RETURNING co.country_id, v.country_name, + v.city_id, v.city_name, v.population; + +\set QUIET true + +SELECT * FROM city_view; + +DROP TABLE city_table CASCADE; +DROP TABLE country_table; |