Update columns in the same table in a deferred constraint trigger
Hi all.
I have this simple schema:
create table fisk(
name varchar primary key,
autofisk varchar
);
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;
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
FOR EACH ROW EXECUTE PROCEDURE fisk_tf();
FOR EACH ROW EXECUTE PROCEDURE fisk_tf();
The problem is that "autofisk" is never populated:
andreak=# begin;
BEGIN
andreak=# insert into fisk(name) values ('a');
INSERT 0 1
andreak=# commit;
NOTICE: name a
COMMIT
andreak=# table fisk;
name | autofisk
------+----------
a |
(1 row)
BEGIN
andreak=# insert into fisk(name) values ('a');
INSERT 0 1
andreak=# commit;
NOTICE: name a
COMMIT
andreak=# table fisk;
name | autofisk
------+----------
a |
(1 row)
Is it possible to do what I want, namely to update a column in a table in an AFTER INSERT OR UPDATE constraint trigger on the same table?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963