From: | Eugen Konkov <kes-kes(at)yandex(dot)ru> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>, pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Does 'instead of delete' trigger support modification of OLD |
Date: | 2019-11-09 12:05:02 |
Message-ID: | 247609768.20191109140502@yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs pgsql-hackers |
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
--
Best regards,
Eugen Konkov
From | Date | Subject | |
---|---|---|---|
Next Message | Eugen Konkov | 2019-11-09 12:10:13 | Re: Does 'instead of delete' trigger support modification of OLD |
Previous Message | Fabien COELHO | 2019-11-09 08:19:16 | Re: Add A Glossary |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2019-11-09 12:07:27 | Re: CountDBSubscriptions check in dropdb |
Previous Message | Tomas Vondra | 2019-11-09 11:52:25 | Re: ssl passphrase callback |