Function reference¶

All functions live in the pgauto_mv schema.

Registration¶

Function	What it does
`register_mv(p_mv_name, p_mv_schema, p_watch_tables, p_refresh_lag, p_max_wait, p_cooldown, p_concurrent, p_fire_on_replica, p_populate_if_empty, p_schedule_name, p_schedule, p_schedule_interval_hours, p_schedule_interval_minutes, p_auto_mv_id)`	Register an existing materialised view for auto-refresh. Returns `auto_mv_id` (UUID). `p_populate_if_empty` (default `true`): if the view is unpopulated at registration, calls `refresh_materialised_view_now()` internally. Pass `false` for bulk-load workflows that populate it manually afterwards.
`unregister_mv(p_mv_schema, p_mv_name)`	Remove auto-refresh management. Does not drop the view.
`update_mv(p_mv_schema, p_mv_name, ...)`	Change timing, schedule, active state, or watch tables. NULL parameters keep their current value.

Function	What it does
`mv_watch(p_table_ref, p_events)`	Build a watch specification. `p_events` defaults to `'INSERT,UPDATE,DELETE'`.
`pgrelay.daily(times)` / `pgrelay.weekdays(times)` / `pgrelay.weekends(times)` / `pgrelay.on_dow(dow, times)`	Build a `pgrelay.schedule` value for the schedule functions. From pg_relay, not pg_auto_mv — see the Schedule Type docs.
`pgrelay.on_day_times('mon=0500', 'tue=1730,2000', …)`	Build a `pgrelay.schedule` value with different times on different days, in one value.

Function	What it does
`get_mv_status()`	Current state of all registered auto_mvs with computed timing and schedule info.
`get_mv(p_mv_name, p_mv_schema, p_mv_oid)`	Full detail for one view: config, watch tables (JSON), schedule definition (JSON), and current state (JSON).
`list_mv(p_schema, p_status, p_include_missing)`	List registered views; optionally include unregistered ones found in the database.

Function	What it does
`refresh_materialised_view_now(p_mv_schema, p_mv_name)`	Queue an immediate refresh bypassing `refresh_lag`/`max_wait`, honouring `cooldown`. EXECUTE revoked from PUBLIC — grant explicitly.

Function	What it does
`create_mv_schedule(p_name, p_schedule, p_interval_hours, p_interval_minutes)`	Create a named schedule. `p_schedule` is a `pgrelay.schedule[]`. Returns `schedule_id`.
`set_mv_schedule_day(p_name, p_dow, p_times)`	Add or replace one day's times in a `'time'` schedule, leaving other days alone. NULL or empty clears that day; clearing the last remaining day raises.
`update_mv_schedule(p_name, p_new_name, p_schedule, p_interval_hours, p_interval_minutes)`	Rename, replace the whole recurrence spec, or change interval.
`delete_mv_schedule(p_name)`	Delete a schedule. Raises error if any view still references it.

Function	What it does
`chain_mv(p_parent_mv, p_child_mv)`	Create a chain link. Format: `'schema.view_name'`.
`unchain_mv(p_parent_mv)`	Remove the chain link from a parent view.

Function	What it does
`get_mv_problems(p_problem_code)`	Report problems with registered views. Default: all problem codes.
`get_mv_refresh_frequency(p_mv_schema, p_mv_name, p_threshold_secs)`	Rolling refresh counts and interval statistics.
`get_view_tables(p_mv_schema, p_mv_name)`	Recursively find all base tables read by a view or materialised view.
`purge_mv_logs(p_auto_mv_id, p_refresh_log_days, p_events_days, p_audit_history_days)`	Delete old rows from log and audit tables. `-1` = skip that table.

Table	Contents
`pgauto_mv.mv_events`	One row per trigger firing or direct call. `action`: `'opened'`, `'updated'`, `'direct'`. `operation`: `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `NONE`.
`pgauto_mv.mv_refresh_log`	One row per refresh attempt. `skip_reason` set when a scheduled slot is skipped. `refresh_source`: `'event'`, `'schedule'`, `'chain'`, or `'direct'`.
`pgauto_mv.mv_audit_history`	REGISTER / UPDATE / UNREGISTER lifecycle records with JSON snapshots.

Table	Contents
`pgauto_mv.schedules`	One row per named schedule. `'time'` schedules hold their recurrence in `schedule_spec pgrelay.schedule[]`; `'interval'` schedules use `interval_seconds`.
`pgauto_mv.mv_problem_codes`	Problem code registry. Set `report = false` on any row to suppress that code.