Trigger to update Materialised view after an insert on another materialised view Postgresql

user_5427124 0
Viewed 119

I am trying to trigger a refresh for a materialised view after an Insert,update or delete on another materialised view. I am trying to use a triggered function to achieve this. Script is as follows;

CREATE FUNCTION aza_ods_version1.populate_fact_churn_m() 
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW aza_ods_version1.fact_churn_monthly;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER refresh_fact_churn_monthly
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON aza_ods_version1.fact_transactions
  FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
  EXECUTE PROCEDURE aza_ods_version1.populate_fact_churn_m();

On attempting to create the trigger, I get this error; ERROR: "fact_transactions" is not a table or view SQL state: 42809

I think its because fact_transactions is a materialized view and not a view or table. Is there a way i can achieve what i am trying to??

triggers postgresql
2 Answers

You can't.
The documentation for CREATE TRIGGER doesn't mention materialized views:

table_name The name (optionally schema-qualified) of the table, view, or foreign table the trigger is for.
user_55794630

You cannot put a trigger on a materialized view, you will need to put the trigger on each table references in the query that generated the materialized. Further for TRUNCATE you will need to create a separate trigger:

In addition, triggers may be defined to fire for TRUNCATE, though only FOR EACH STATEMENT.

user_76238560
Related