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
