Re: Update columns in the same table in a deferred constraint trigger
От | Andreas Joseph Krogh |
---|---|
Тема | Re: Update columns in the same table in a deferred constraint trigger |
Дата | |
Msg-id | VisenaEmail.3b.53bbc6fc6d6c2c43.1478192cae2@tc7-on обсуждение исходный текст |
Ответ на | Re: Update columns in the same table in a deferred constraint trigger (Pavel Stehule <pavel.stehule@gmail.com>) |
Ответы |
Re: Update columns in the same table in a deferred constraint trigger
|
Список | pgsql-sql |
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <pavel.stehule@gmail.com>:
2014-07-29 11:59 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <pavel.stehule@gmail.com>:Hi2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andreas@visena.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 DEFERREDIt should be BEFORE INSERT OR UPDATE triggerHe 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?
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?
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
В списке pgsql-sql по дате отправления: