Date: Wed, 6 Nov 2019 13:59:35 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Eugen Konkov <kes-kes@yandex.ru>
Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>,
	pgsql-docs@lists.postgresql.org
Subject: Re: Does 'instead of delete' trigger support modification of OLD
Message-ID: <20191106185935.GE1843@momjian.us>
References: <919823407.20191029175436@yandex.ru>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="mxv5cy4qt+RJ9ypb"
Content-Disposition: inline
In-Reply-To: <919823407.20191029175436@yandex.ru>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--mxv5cy4qt+RJ9ypb
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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@momjian.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 +

--mxv5cy4qt+RJ9ypb
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="trigger.diff"

diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
new file mode 100644
index 67e1861..f62f420
*** a/doc/src/sgml/trigger.sgml
--- b/doc/src/sgml/trigger.sgml
***************
*** 230,236 ****
      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
!     <command>INSERT</command> and <command>UPDATE</command> operations, the trigger
      may modify the <varname>NEW</varname> row before returning it.  This will
      change the data returned by
      <command>INSERT RETURNING</command> or <command>UPDATE RETURNING</command>,
--- 230,236 ----
      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
!     <command>INSERT</command> and <command>UPDATE</command> operations only, the trigger
      may modify the <varname>NEW</varname> row before returning it.  This will
      change the data returned by
      <command>INSERT RETURNING</command> or <command>UPDATE RETURNING</command>,

--mxv5cy4qt+RJ9ypb--