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?
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.
