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

Hello Eugen,

Saturday, November 9, 2019, 2:05:02 PM, you wrote:

> Hello Bruce,

> Friday, November 8, 2019, 12:28:18 AM, you wrote:

>> On Thu, Nov 7, 2019 at 04:26:55PM -0500, Bruce Momjian wrote:
>>> On Thu, Nov 7, 2019 at 11:24:29AM +0200, Eugen Konkov wrote:
>>> > >> 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.
>>> >
>>> > > Yes, Add please into TODO the feature to "allowing DELETE to modify the trigger row
>>> > > for RETURNING". Becuase, as I have described at first letter, without
>>> > > this the RETURNING rows **does not correspond actually deleted data**
>>> >
>>> > > Thank you.
>>>
>>> I have added a TODO item:
>>>
>>> Allow DELETE triggers to modify rows, for use by RETURNING

>> Thinking some more on this, I now don't think a TODO makes sense, so I
>> have removed it.

>> Triggers are designed to check and modify input data, and since DELETE
>> has no input data, it makes no sense. In the attached SQL script, you
>> can see that only the BEFORE INSERT trigger fires, so there is no way
>> even with INSERT to change what is passed after the write to RETURNING.
>> What you can do is to modify the returning expression, which is what I
>> have done for the last query --- hopefully that will help you.

> You lost my idea. First of all I am talking about views and an
> INSTEAD OF triggers.

> INSERT/UPDATE operation present which data is added into DB
> DELETE operation present which data is deleted from DB
> (in my case I am not deleted exact that data which matched by where.
> See example below)

> Thus INSTEAD OF INSERT/UPDATE triggers are designed to check and modify input data
> eg. we can insert/update something different then incoming data (here
> we are modifying NEW)

> Thus INSTEAD OF DELETE triggers are designed to check and delete **output** data
> eg. we can delete something different then underlaid data (here we are
> modifying OLD)

> for example, we have next data: 1 2 3 4 5 6 7 8
> it is not presented by eight rows, but instead it is presented as one
> row with range data type: [1..8]

> When we insert data we will not get new row, we change current:
> insert into table values ( 9 ) will result
> [1..9]
> instead of
> [1..8]
> 9

> So lets look into INSTEAD OF DELETE trigger when we deleting
> data:
> delete from table where x in ( 5, 6, 7 );
> after deleting this we should get:
> [1..4]
> [8..9]

> thus
> with t1 as ( delete from table where x in ( 5, 6, 7 ) returning * )
> select * from t1
> should return:
> [5..7]
> instead of
> [1..9]
> because we does not delete ALL [1..9], we just delete ONLY [5..7]

> Thus I need to change matched row OLD.x from [1..9] to [5..7]

> Please reread my first letter. There I describe more real life example
> when I am manipulating bi-temporal data.

> where some value exist at given period:
> id | app_period | value
> 7 [2019-01-01, 2019-04-05) 207

> And I am deleting third month: [ 2019-03-01, 2019-04-01 )
> with t1 as ( delete from table where app_period && [ 2019-03-01,
> 2019-04-01 ) returning * )
> select * from t1;
> 7 [ 2019-03-01, 2019-04-01 ) 207

> select * from table;
> 7 [ 2019-01-01, 2019-03-01 ) 207
> 7 [ 2019-04-01, 2019-04-05 ) 207

Here when data is deleted the next row is matched:
7 [2019-01-01, 2019-04-05) 207
and assigned to OLD.
Because I am deleting data ONLY from [ 2019-03-01, 2019-04-01 ) period
I am required to change OLD:

OLD.app_period = [ 2019-03-01, 2019-04-01 )

So I should get:
> 7 [ 2019-03-01, 2019-04-01 ) 207
instead of
> 7 [2019-01-01, 2019-04-05) 207

--
Best regards,
Eugen Konkov