Re: Does 'instead of delete' trigger support modification of OLD

On Tue, Oct 29, 2019 at 05:54:36PM +0200, Eugen Konkov wrote:
> Hi.
>
> This is not clear from doc, so I have asked on IRC too.
>
> from the DOC: https://www.postgresql.org/docs/current/trigger-definition.html
> In the case of INSTEAD OF triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger
>
> I modify OLD row, thus I expect to get modified version when run next query:
>
> WITH t1 AS( delete from abc returning *)
> select * from t1;
>
> fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=637730305f66bf531794edb09a462c95

Wow, that is a very nice way to present the queries.

> > https://www.postgresql.org/docs/current/trigger-definition.html
> A row-level INSTEAD OF trigger should either return NULL to indicate that it did not modify any data from the view's underlying base tables,
> or it should return the view row that was passed in (the NEW row for INSERT and UPDATE operations, or the OLD row for DELETE operations).
> A nonnull return value is 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, 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.
>
> But I still does not understand. Doc explicitly do not prohibit modification of OLD and has no examples for DELETE RETURNING case

I looked in the CREATE TRIGGER manual page and found this:

https://www.postgresql.org/docs/12/sql-createtrigger.html
If the trigger fires before or instead of the event, the trigger
can skip the operation for the current row, or change the row
being inserted (for INSERT and UPDATE operations only).

I don't see the "(for INSERT and UPDATE operations only)" language in
the main trigger documentation,
https://www.postgresql.org/docs/current/trigger-definition.html. I have
written the attached patch to fix that. Does that help?

As far as allowing DELETE to modify the trigger row for RETURNING, I am
not sure how much work it would take to allow that, but it seems like it
is a valid requite, and if so, I can add it to the TODO list.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +