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.
a | b """"""" |
c | d """"""" |
a | c """"""" |
a | d """"""" |
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();