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

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.

Regards

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>
>
>