POSTGRESQL: Refresh the materialized view which effect when there are change based on columns

Question

I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works fine. Can someone help me to make a function so that the trigger only refresh the affected matview based on changes in the column?

for example table_1 has columns a and b. table 2 has columns c and d. matview_1 takes columns a and c, matview_2 takes columns a and d, matview_3 takes columns b and c. I need if there is a changes in a row of table 1 column a, only matview_1 and matview_2 is refresh by the store procedure trigger. but if there are changes in row of table_1 column b, only matview_3 is refresh.

table_1

a  |  b
"""""""
   |

table_2

c  |  d
"""""""
   |

matview_1

a  |  c
"""""""
   |

matview_2

a  |  d
"""""""
   |

matview_3

b  |  c
"""""""
   |

Here my function trigger for all table:

CREATE OR REPLACE FUNCTION sp_refresh_matview() RETURNS trigger AS
$$
DECLARE
rec RECORD;
BEGIN
    FOR rec IN
        SELECT *
        FROM pg_matviews WHERE (
            definition LIKE '%'||TG_TABLE_NAME||'%')        
    LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW '|| rec.matviewname; 
    END LOOP; 
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

Here my create trigger:

CREATE TRIGGER trigger_tabel AFTER
INSERT OR UPDATE OR DELETE ON name_table
FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview();

Answer 1

The most efficient solution is to not fire the trigger in that case:

CREATE TRIGGER trigger_tabel 
AFTER INSERT 
   OR UPDATE OF (column_a) --<< only fire the trigger if column_a is updated
   OR DELETE 
ON name_table
FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview();

If you have multiple columns that should trigger the refresh, you can separate them using commas: OR UPDATE OF (column_a, column_c, column_d)

---

A less efficient solution is to check if the column changed inside the trigger function:

CREATE OR REPLACE FUNCTION sp_refresh_matview() RETURNS trigger A
$$
DECLARE
rec RECORD;
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'DELETE' OR 
       -- add all columns that need to be checked for changes
       (old.column_b IS DISTINCT FROM new.column_b) 
    THEN
      FOR rec IN
        SELECT *
        FROM pg_matviews WHERE (
            definition LIKE '%'||TG_TABLE_NAME||'%')        
      LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW '|| rec.matviewname; 
      END LOOP; 
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

If you are using the same trigger function for all tables, then obviously this only works if all tables have the same columns that you want to check. But not firing the trigger to begin with will be much more efficient and should be your preferred solution.