Re: 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
);

 

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

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)

 

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

andreas@visena.com

www.visena.com