Postgres Pro
Downloads
Home   >   mailing lists

Thread: Coalesce in PostgreSQL trigger does not fire on upddate

Coalesce in PostgreSQL trigger does not fire on upddate

From
ALT SHN
Date:
(This question is also exposed here: https://stackoverflow.com/questions/60117123/coalesce-in-postgresql-trigger-does-not-fire-on-upddate )

Hi Everyone!

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

Thanks,
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

Re: Coalesce in PostgreSQL trigger does not fire on upddate

From
Christian Barthel
Date:
ALT SHN <i.geografica@alt-shn.org> writes:

[..]
> 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? 

I have just tested it on PostgreSQL 11 (FreeBSD) and it seems to
work (at least, it is what I would expect):


bch=# select id,taxon,reino,phylum,subphylum,classe,especie from
 taxon where id = 3; 
 id | taxon | reino | phylum | subphylum | classe | especie 
----+-------+-------+--------+-----------+--------+---------
(0 rows)

bch=# insert into taxon(reino,classe) values ('1', '2');
INSERT 0 1
bch=# select id,taxon,reino,phylum,subphylum,classe,especie from
taxon where id = 3; 
 id | taxon | reino | phylum | subphylum | classe | especie 
----+-------+-------+--------+-----------+--------+---------
  3 | 2     | 1     |        |           | 2      | 
(1 row)

bch=# update taxon set especie='99' where id = 3;
UPDATE 1
bch=# select id,taxon,reino,phylum,subphylum,classe,especie from
taxon where id = 3; 
 id | taxon | reino | phylum | subphylum | classe | especie 
----+-------+-------+--------+-----------+--------+---------
  3 | 99    | 1     |        |           | 2      | 99
(1 row)

Are there any other triggers preventing the update?  You may also
write some RAISE NOTICE messages into the trigger to see when it
is being executed.

-- 
Christian Barthel <bch@online.de>

Re: Coalesce in PostgreSQL trigger does not fire on upddate

From
ALT SHN
Date:
Thanks Christian,

Yes there is another update trigger that seems to be interfering. I updated the question with the details of this second trigger here: https://stackoverflow.com/questions/60117123/coalesce-in-postgresql-trigger-does-not-fire-on-update

Christian Barthel <bch@online.de> escreveu no dia sábado, 8/02/2020 à(s) 08:53:
ALT SHN <i.geografica@alt-shn.org> writes:

[..]
> 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?

I have just tested it on PostgreSQL 11 (FreeBSD) and it seems to
work (at least, it is what I would expect):


bch=# select id,taxon,reino,phylum,subphylum,classe,especie from
 taxon where id = 3;
 id | taxon | reino | phylum | subphylum | classe | especie
----+-------+-------+--------+-----------+--------+---------
(0 rows)

bch=# insert into taxon(reino,classe) values ('1', '2');
INSERT 0 1
bch=# select id,taxon,reino,phylum,subphylum,classe,especie from
taxon where id = 3;
 id | taxon | reino | phylum | subphylum | classe | especie
----+-------+-------+--------+-----------+--------+---------
  3 | 2     | 1     |        |           | 2      |
(1 row)

bch=# update taxon set especie='99' where id = 3;
UPDATE 1
bch=# select id,taxon,reino,phylum,subphylum,classe,especie from
taxon where id = 3;
 id | taxon | reino | phylum | subphylum | classe | especie
----+-------+-------+--------+-----------+--------+---------
  3 | 99    | 1     |        |           | 2      | 99
(1 row)

Are there any other triggers preventing the update?  You may also
write some RAISE NOTICE messages into the trigger to see when it
is being executed.

--
Christian Barthel <bch@online.de>


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


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