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