Watching multiple tables

If your view joins more than one source table, register it to watch all of them. A change to any one triggers a refresh.

-- A view that joins orders and order_lines
CREATE MATERIALIZED VIEW reporting.order_detail_summary AS
SELECT
    o.id                        AS order_id,
    o.customer_id,
    o.created_at::date          AS order_date,
    count(l.id)                 AS line_count,
    sum(l.unit_price * l.qty)   AS total
FROM sales.orders o
JOIN sales.order_lines l ON l.order_id = o.id
GROUP BY 1, 2, 3;

CREATE UNIQUE INDEX ON reporting.order_detail_summary (order_id);
REFRESH MATERIALIZED VIEW reporting.order_detail_summary;

-- Watch both source tables
SELECT pgauto_mv.register_mv(
    p_mv_name      => 'order_detail_summary',
    p_mv_schema    => 'reporting',
    p_watch_tables => ARRAY[
        pgauto_mv.mv_watch('sales.orders', 'UPDATE,DELETE'),  -- only watch for update, delete (and truncate) events
        pgauto_mv.mv_watch('sales.order_lines')               -- watch for all events, so insert, update, delete (and truncate) events are watched
    ],
    p_refresh_lag  => 5.0,
    p_max_wait     => 30.0,
    p_cooldown     => 10.0
);

Watching only specific events:

If the view only changes when orders are inserted (not updated or deleted), narrow the watch:

pgauto_mv.mv_watch('sales.orders', 'INSERT')

The timing parameters apply to the view as a whole — it does not matter which table triggered the change.

---