diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 6e1f370b21..1d6e58312b 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -262,13 +262,15 @@
used to signal that the trigger performed the necessary data
modifications in the view. This will cause the count of the number
of rows affected by the command to be incremented. For
- INSERT and UPDATE operations only, the trigger
- may modify the NEW row before returning it. This will
- change the data returned by
- INSERT RETURNING or UPDATE RETURNING,
- and is useful when the view will not show exactly the same data
- that was provided.
-
+ INSERT, UPDATE, and
+ DELETE operations, INSTEAD OF
+ triggers can modify the data returned by RETURNING. In
+ the case of INSERT and UPDATE,
+ triggers can modify the NEW row before returning it,
+ while for DELETE, triggers can modify the
+ OLD row before returning it. This feature is useful when
+ the returned data needs to be adjusted to match the view or other
+ requirements.
The return value is ignored for row-level triggers fired after an
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 52177759ab..383f8021a5 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2815,10 +2815,11 @@ ExecARDeleteTriggers(EState *estate,
bool
ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
- HeapTuple trigtuple)
+ TupleTableSlot *slot)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
- TupleTableSlot *slot = ExecGetTriggerOldSlot(estate, relinfo);
+ HeapTuple newtuple = NULL;
+ bool should_free;
TriggerData LocTriggerData = {0};
int i;
@@ -2828,12 +2829,10 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_EVENT_INSTEAD;
LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
- ExecForceStoreHeapTuple(trigtuple, slot, false);
-
for (i = 0; i < trigdesc->numtriggers; i++)
{
- HeapTuple rettuple;
Trigger *trigger = &trigdesc->triggers[i];
+ HeapTuple oldtuple;
if (!TRIGGER_TYPE_MATCHES(trigger->tgtype,
TRIGGER_TYPE_ROW,
@@ -2844,18 +2843,33 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
NULL, slot, NULL))
continue;
+ if (!newtuple)
+ newtuple = ExecFetchSlotHeapTuple(slot, true, &should_free);
+
LocTriggerData.tg_trigslot = slot;
- LocTriggerData.tg_trigtuple = trigtuple;
+ LocTriggerData.tg_trigtuple = oldtuple = newtuple;
LocTriggerData.tg_trigger = trigger;
- rettuple = ExecCallTriggerFunc(&LocTriggerData,
+ newtuple = ExecCallTriggerFunc(&LocTriggerData,
i,
relinfo->ri_TrigFunctions,
relinfo->ri_TrigInstrument,
GetPerTupleMemoryContext(estate));
- if (rettuple == NULL)
+ if (newtuple == NULL)
+ {
+ if (should_free)
+ heap_freetuple(oldtuple);
return false; /* Delete was suppressed */
- if (rettuple != trigtuple)
- heap_freetuple(rettuple);
+ }
+ else if (newtuple != oldtuple)
+ {
+ ExecForceStoreHeapTuple(newtuple, slot, false);
+
+ if (should_free)
+ heap_freetuple(oldtuple);
+
+ /* signal tuple should be re-fetched if used */
+ newtuple = NULL;
+ }
}
return true;
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index f62d28ac60..5c3717442c 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1451,7 +1451,11 @@ ExecDelete(ModifyTableContext *context,
bool dodelete;
Assert(oldtuple != NULL);
- dodelete = ExecIRDeleteTriggers(estate, resultRelInfo, oldtuple);
+
+ slot = ExecGetReturningSlot(estate, resultRelInfo);
+ ExecForceStoreHeapTuple(oldtuple, slot, false);
+
+ dodelete = ExecIRDeleteTriggers(estate, resultRelInfo, slot);
if (!dodelete) /* "do nothing" */
return NULL;
@@ -1672,7 +1676,13 @@ ldelete:
*/
TupleTableSlot *rslot;
- if (resultRelInfo->ri_FdwRoutine)
+ if (resultRelInfo->ri_TrigDesc &&
+ resultRelInfo->ri_TrigDesc->trig_delete_instead_row)
+ {
+ /* INSTEAD OF trigger handling should have provided a slot */
+ Assert(!TupIsNull(slot));
+ }
+ else if (resultRelInfo->ri_FdwRoutine)
{
/* FDW must have provided a slot containing the deleted row */
Assert(!TupIsNull(slot));
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 430e3ca7dd..09d3d66447 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -222,7 +222,7 @@ extern void ExecARDeleteTriggers(EState *estate,
bool is_crosspart_update);
extern bool ExecIRDeleteTriggers(EState *estate,
ResultRelInfo *relinfo,
- HeapTuple trigtuple);
+ TupleTableSlot *slot);
extern void ExecBSUpdateTriggers(EState *estate,
ResultRelInfo *relinfo);
extern void ExecASUpdateTriggers(EState *estate,
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index e8de916dfe..657dc8109f 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1385,6 +1385,15 @@ end;
$$;
CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view
FOR EACH ROW EXECUTE PROCEDURE city_update();
+CREATE VIEW static_view AS SELECT 1 AS a;
+CREATE FUNCTION static_view_delete() RETURNS trigger LANGUAGE plpgsql AS $$
+begin
+ OLD.a := OLD.a + 3;
+ RETURN OLD;
+end;
+$$;
+CREATE TRIGGER static_delete_trig INSTEAD OF DELETE ON static_view
+FOR EACH ROW EXECUTE PROCEDURE static_view_delete();
\set QUIET false
-- INSERT .. RETURNING
INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *;
@@ -1478,6 +1487,13 @@ DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *;
234567 | Birmingham | 1016800 | UK | Europe
(1 row)
+DELETE 1
+DELETE FROM static_view RETURNING *;
+ a
+---
+ 4
+(1 row)
+
DELETE 1
\set QUIET true
-- read-only view with WHERE clause
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index d29e98d2ac..80f9512ec8 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -960,6 +960,18 @@ $$;
CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view
FOR EACH ROW EXECUTE PROCEDURE city_update();
+CREATE VIEW static_view AS SELECT 1 AS a;
+
+CREATE FUNCTION static_view_delete() RETURNS trigger LANGUAGE plpgsql AS $$
+begin
+ OLD.a := OLD.a + 3;
+ RETURN OLD;
+end;
+$$;
+
+CREATE TRIGGER static_delete_trig INSTEAD OF DELETE ON static_view
+FOR EACH ROW EXECUTE PROCEDURE static_view_delete();
+
\set QUIET false
-- INSERT .. RETURNING
@@ -983,6 +995,7 @@ UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2
-- DELETE .. RETURNING
DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *;
+DELETE FROM static_view RETURNING *;
\set QUIET true