public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: Eugen Konkov <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: [email protected]
Subject: Re: Does 'instead of delete' trigger support modification of OLD
Date: Wed, 6 Nov 2019 13:59:35 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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 <[email protected]> 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 +
Attachments:
[text/x-diff] trigger.diff (1.2K, 2-trigger.diff)
download | inline 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>,
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Does 'instead of delete' trigger support modification of OLD
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox