Re: Coalesce in PostgreSQL trigger does not fire on upddate

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>