Coalesce in PostgreSQL trigger does not fire on update

Question

In the context of a database for a paleontological collection, I have this table definition in PostgreSQL v.12:

CREATE TABLE taxon (
id                  integer  DEFAULT NEXTVAL('taxon_oid_seq')   PRIMARY KEY,
taxon                varchar(100)         UNIQUE  NOT NULL,
reino                varchar(50)                  NOT NULL,
phylum               varchar(100)         ,
subphylum            varchar(100)         ,
classe               varchar(100)         ,
subclasse            varchar(100)         ,
superordem           varchar(100)         ,
ordem                varchar(100)         ,
subordem             varchar(100)         ,
infraordem           varchar(100)         ,
familia              varchar(100)         ,
subfamilia           varchar(100)         ,
genero               varchar(100)         ,
especie              varchar(100)         ,
subespecie           varchar(100)             );

The taxon field is to be automatically filled with the lowest level to which it was possible to determine a given species taxonomy. In order to achieve that I have this trigger:

CREATE OR REPLACE FUNCTION get_taxon() RETURNS TRIGGER LANGUAGE
plpgsql AS $BODY$ 
BEGIN   
NEW.taxon := coalesce(NEW.subespecie, NEW.especie, NEW.genero, NEW.subfamilia, 
                      NEW.familia, NEW.infraordem, NEW.subordem, NEW.ordem, NEW.superordem,
                      NEW.subclasse, NEW.classe, NEW.subphylum, NEW.phylum, NEW.reino);   
RETURN NEW; 
END; 
$BODY$ 
VOLATILE; 

CREATE TRIGGER update_taxon
BEFORE INSERT OR UPDATE ON taxon
FOR EACH ROW EXECUTE PROCEDURE get_taxon();

However this trigger only fires on INSERT, nothing happens if an UPDATE is made. How can have this trigger also firing in the case of an UPDATE?

EDIT

I just realized that I have another trigger that fires on update. This other trigger is defined as:

CREATE OR REPLACE FUNCTION taxon_history_update() RETURNS trigger AS 
    $BODY$
    BEGIN
            INSERT INTO history.taxon(operacao, data, tecnico, original_oid, 
                                      taxon, reino, phylum, subphylum, classe, 
                                      subclasse, superordem, ordem, subordem, 
                                      infraordem, familia, subfamilia, genero, 
                                      especie, subespecie) 
                                VALUES        
                                      ('UPDATE', current_timestamp, current_user, 
                                       old.oid, old.taxon, old.reino, old.phylum, 
                                       old.subphylum, old.classe, old.subclasse, 
                                       old.superordem, old.ordem, old.subordem, 
                                       old.infraordem, old.familia, 
                                       old.subfamilia, old.genero, old.especie, 
                                       old.subespecie);
                                       RETURN old;
    END;
    $BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER taxon_history_update
BEFORE UPDATE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_update();

Could it be there is some kind of conflict/interferance bewteen these two triggers? My update_taxon trigger works also for updates provided I drop the taxon_history_update trigger!

Answer 1

Your problem is the second trigger. It is a BEFORE UPDATE trigger that returns old rather than new as it should.

That means that the original row (old) will be written to the table rather than the updated row (new). So your update will do nothing.

Since BEFORE UPDATE triggers are executed in alphabetical order, the update_taxon trigger will get the old row instead of the new row as new.

Fix the taxon_history_update function to RETURN new;, and all your troubles will be gone.