Re: Update columns in the same table in a deferred constraint trigger

2014-07-29 12:21 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>:
>
>
>
> 2014-07-29 12:05 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>
>> På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
>> pavel(dot)stehule(at)gmail(dot)com>:
>>
>>
>>
>> 2014-07-29 11:59 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>>
>>> På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
>>> pavel(dot)stehule(at)gmail(dot)com>:
>>>
>>> Hi
>>>
>>> 2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>>>
>>>> Hi all.
>>>>
>>>> I have this simple schema:
>>>>
>>>> create table fisk(
>>>> name varchar primary key,
>>>> autofisk varchar
>>>> );
>>>>
>>>> I want to update the column "autofisk" on commit based the value of
>>>> "name", so I created this trigger:
>>>>
>>>> CREATE OR REPLACE FUNCTION fisk_tf() returns TRIGGER AS $$
>>>> BEGIN
>>>> raise notice 'name %', NEW.name;
>>>> NEW.autofisk = NEW.name || CURRENT_TIMESTAMP::text;
>>>> RETURN NEW;
>>>> END;
>>>> $$ LANGUAGE plpgsql;
>>>>
>>>> CREATE CONSTRAINT TRIGGER fisk_t AFTER INSERT OR UPDATE ON fisk
>>>> DEFERRABLE INITIALLY DEFERRED
>>>>
>>>
>>> It should be BEFORE INSERT OR UPDATE trigger
>>>
>>>
>>> He he, yes - I know that will work, but I need the trigger to be run
>>> as a constraint-trigger, on commit, after all the data is populated in
>>> other tables and this table.
>>>
>>
>> It is not possible - Postgres can change data only before writing
>>
>>
>> Is there a work-around, so I in the trigger can issue for example:
>>
>> update fisk set autofisk = NEW.name || CURRENT_TIMESTAMP::text where
>> name = NEW.name;
>>
>> without it also triggering the trigger?
>>
>
> theoretically yes - you can disable triggers via ALTER TABLE DISABLE
> TRIGGER
>
> but then the code will be unmaintainable. Anything else is better than
> dependency in triggers. You should to think about different solution.
>
> Sometimes triggers can be replaced by functions directly called from
> applications instead DML statements.
>
>
>>
>> I have tried this but the commit never returns, I think because it
>> recursively triggers the trigger again for that modification.
>>
>> Will temporarily disabeling the trigger inside the trigger (in a
>> transaction) work?
>>
>
> I really afraid of this strategy
>
>
> I see, so it boils down to this being impossible at the moment.
> I really want this to be at the DML-level so any modification done also
> updates the "autofisk"-column.
>
> Are there any plans to make this work, that being modifying the same table
> in a trigger running on it where the modification (comming form statements
> inside the trigger-functino) like what I'm trying will not trigger the
> trigger?
>

you can use a auxiliary column with information where are from a UPDATE.
This information should be used for breaking recursion. But it is not a
good solution. You do some too complex.

Why you need it?

Pavel

>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>