PostgreSQL : Refresh a materialized view base on a foreign table

Question

I've got a foreign table from a distant csv host on a external webserver.

I need to enhance this table so I create a materialized view to add some extra fields and join with internal data. Making a view not working, I've got a error (probably due to performance issue of distant http server)

I can't use some bash cron script on the Linux host as IT department not wanted so I need to stay in PostgreSQL capabilities it self.

I've try this method but it seems I can't add a trigger on a foreign table.

Any way to refresh materialized view ? (each time distant csv is updated but could be also based on a frequency, for example each 24h)

---

EDIT to be more efficient : How to refresh a materialized view from a foreign table ?

• Bad Wolf: A table with data from remote source. Using the pg extension file_fdw see postgresql.org/docs/13/file-fdw.html

Answer 1

As trigger cannot be set on Foreign Table, I've find an other way by using Pg_Cron extension