Refresh a PostgreSQL materialized view automatically without using triggers

Question

I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this with triggers but it didn't work.

Is it possible to refresh a materialized view automatically without using triggers?

• phil294: Why didnt it work using triggers? It should (?)

Answer 1

As a_horse_with_no_name said in a comment:

No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name

Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. Materialization only adds a periodic cache. It's only needed when the query itself is prohibitively slow or hot.