Coalesce in PostgreSQL trigger does not fire on upddate - Mailing list pgsql-novice

In the context of a database for a paleontological collection, I have this table definition:

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();

Andre 

--

---------------------------------------------------------------


Sociedade de História Natural
Departamento de Informação Geográfica
Polígono Industrial do Alto do Amial
Pav.H02 e H06

i.geografica@alt-shn.org
www.shn.pt
www.alt-shn.blogspot.com

Facebook

TLM: 964138188