Automatically call function after materialized view refresh?

Question

Most question I've seen regarding materialized views and automation concerned either refreshing views periodically or refreshing them on some trigger.

It could be said, that my problem is reverse of that. Given some materialized view, I'd like to call some function that inserts some rows into some other table. So, basically something like "create trigger after refresh on ". The problem is, there's no such trigger type as "after refresh".

Is it achievable natively in Postgres?

Interesting use case! I'm not aware of such triggers. Would a trigger on the origin table(s) to call this function be an option? — Jim Jones
– Jim Jones, Commented Dec 18, 2020 at 13:21

Laurenz Albe · Accepted Answer · 2020-12-18 14:12:31Z

2

The only way to do that is with an event trigger that fires ON ddl_command_end.

Check for TG_TAG = 'REFRESH MATERIALIZED VIEW' in the function body. Check the objid in the result of pg_event_trigger_ddl_commands() to know which materialized view was refreshed.

answered Dec 18, 2020 at 14:12

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automatically call function after materialized view refresh?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related