How to make a PG function and trigger from SQL syntax?

I’m currently using this SQL syntax to update all records of one table after checking values in another table:

UPDATE schema.table1 AS c 
SET field1 = 'SI'
FROM schema.table2 AS s
WHERE
c.specie = s.diam20 AND c.specie IS NOT NULL AND c.circonf/PI() >= 0.20
OR
c.specie = s.diam40 AND c.specie IS NOT NULL AND c.circonf/PI() >= 0.40
OR
c.specie = s.diam60 AND c.specie IS NOT NULL AND c.circonf/PI() >= 0.60
OR
c.specie = s.diam80 AND c.specie IS NOT NULL AND c.circonf/PI() >= 0.80;

Sorry I’m a newbie so I’m trying to setup a trigger after each inserting or updating of table 1, that updates field1 values calling a PG function that is able to do as above. Could you please help me? Thanks in advance

Answer

Why not simply create a view to display this derived information, rather than storing data that you then need to manage?

The upside is that you have an always up-to-date perspective at your data, without any maintenance cost.

This should be as simple as:

create view view1 as
select 
    t1.*, 
    case when exists (
        select 1
        from table2 t2
        where t1.specie = case
            when t1.circonf / pi() >= 0.80 then t2.diam20 
            when t1.circonf / pi() >= 0.60 then t2.diam40 
            when t1.circonf / pi() >= 0.40 then t2.diam60 
            when t1.circonf / pi() >= 0.20 then t2.diam80
        end
    ) then 'SI' end as field1
from table1 t1

Side note: you should try and fix your schema – the joining logic indicates that it is not properly normalized. You should store each of the four diam* column of table2 in rows rather than columns, with a range specification in other columns, like:

min_cicronf    max_circonf    diam
0.20 * pi()    0.40 * pi()    ...
0.40 * pi()    0.60 * pi()    ...
0.60 * pi()    0.80 * pi()    ...
0.80 * pi()    null           ...

Then the query could be simplified as the simpler and more efficient:

create view view1 as
select 
    t1.*, 
    case when exists (
        select 1
        from table2 t2
        where 
            t1.specie = t2.diam
            and t1.circonf >= t2.min_cicronf
            and (t1.circonf < t2.max_cicronf or t2.max_circonf is null)
    ) then 'SI' end as field1
from table1 t1