10

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?

10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.