dbmcp_postgres/tools/
list_materialized_views.rs

1//! MCP tool: `listMaterializedViews`.
2
3use std::borrow::Cow;
4
5use dbmcp_server::pagination::Pager;
6use dbmcp_sql::Connection as _;
7use rmcp::handler::server::router::tool::{AsyncTool, ToolBase};
8use rmcp::model::{ErrorData, ToolAnnotations};
9
10use crate::PostgresHandler;
11use crate::types::{ListMaterializedViewsRequest, ListMaterializedViewsResponse};
12
13/// Marker type for the `listMaterializedViews` MCP tool.
14pub(crate) struct ListMaterializedViewsTool;
15
16impl ListMaterializedViewsTool {
17    const NAME: &'static str = "listMaterializedViews";
18    const TITLE: &'static str = "List Materialized Views";
19    const DESCRIPTION: &'static str = r#"List materialized views in the `public` schema, optionally filtered and/or with full metadata. Unlike regular views, materialized views store their results physically and must be refreshed explicitly. Regular views and system-schema matviews are excluded.
20
21<usecase>
22Use when:
23- Auditing materialized views across a database (brief mode, default).
24- Searching for a matview by partial name (pass `search`).
25- Inspecting a matview's owner, comment, full SELECT body, populated state, and index presence before querying or refreshing it (pass `detailed: true`). Detailed mode supersedes ad-hoc `readQuery` against `pg_matviews` / `pg_class`.
26</usecase>
27
28<parameters>
29- `database` — Database to target. Defaults to the active database.
30- `cursor` — Opaque pagination cursor; echo the prior response's `nextCursor`.
31- `search` — Case-insensitive filter on matview names via `ILIKE`. `%` matches any sequence; `_` matches a single character.
32- `detailed` — When `true`, returns full metadata objects keyed by bare matview name instead of bare name strings. Default `false`.
33</parameters>
34
35<examples>
36✓ "What materialized views are in mydb?" → listMaterializedViews(database="mydb")
37✓ "Find the recent-orders matview" → listMaterializedViews(search="orders")
38✓ "What does mv_orders_by_region compute?" → listMaterializedViews(search="mv_orders_by_region", detailed=true)
39✓ "Has the cache matview ever been refreshed?" → listMaterializedViews(search="cache", detailed=true) — read `populated`
40✓ "Which matviews can I refresh concurrently?" → listMaterializedViews(detailed=true) — read `indexed` (CONCURRENTLY additionally needs a unique index)
41✗ "List regular views" → use listViews instead
42</examples>
43
44<what_it_returns>
45Brief mode (default): a sorted JSON array of matview-name strings, e.g. `["mv_archived_orders", "mv_recent_orders"]`. Matview names are unique per schema, so no duplicates appear.
46Detailed mode: a JSON object keyed by bare matview name; each value carries:
47- `schema` — schema name (always `"public"` in this build).
48- `owner` — owning role's name from `pg_matviews.matviewowner`.
49- `description` — `COMMENT ON MATERIALIZED VIEW` text, or `null` when no comment.
50- `definition` — the SELECT body verbatim from `pg_matviews.definition`, with no `CREATE MATERIALIZED VIEW` wrapper.
51- `populated` — `true` once the matview has been refreshed at least once. `false` for matviews created `WITH NO DATA` and never refreshed; querying such a matview returns zero rows until `REFRESH MATERIALIZED VIEW` runs.
52- `indexed` — `true` when at least one index exists on the matview. `REFRESH MATERIALIZED VIEW CONCURRENTLY` additionally requires a unique index; this tool reports the broader has-any-index signal.
53
54The matview name is the map key only — it is not repeated inside the value. Detailed mode deliberately omits column metadata (`columns`), `tablespace`, storage parameters, and unique-index detection. Column shape is recoverable from the `definition` text or via `listTables(detailed=true)` since Postgres exposes matviews in `pg_class`.
55</what_it_returns>
56
57<pagination>
58Paginated. Pass the prior response's `nextCursor` as `cursor` to fetch the next page. The `search` filter must stay the same across pages for cursor continuity. Brief and detailed modes share the same `matviewname` row order, so a client can switch `detailed` between pages without losing position.
59</pagination>"#;
60}
61
62impl ToolBase for ListMaterializedViewsTool {
63    type Parameter = ListMaterializedViewsRequest;
64    type Output = ListMaterializedViewsResponse;
65    type Error = ErrorData;
66
67    fn name() -> Cow<'static, str> {
68        Self::NAME.into()
69    }
70
71    fn title() -> Option<String> {
72        Some(Self::TITLE.into())
73    }
74
75    fn description() -> Option<Cow<'static, str>> {
76        Some(Self::DESCRIPTION.into())
77    }
78
79    fn annotations() -> Option<ToolAnnotations> {
80        Some(
81            ToolAnnotations::new()
82                .read_only(true)
83                .destructive(false)
84                .idempotent(true)
85                .open_world(false),
86        )
87    }
88}
89
90impl AsyncTool<PostgresHandler> for ListMaterializedViewsTool {
91    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
92        handler.list_materialized_views(params).await
93    }
94}
95
96/// Brief-mode SQL: `pg_matviews` scan with `ILIKE` filter on matview name.
97///
98/// `pg_matviews` already filters to `pg_class.relkind = 'm'`, and
99/// `schemaname = 'public'` keeps system-schema matviews out. The
100/// `($1::text IS NULL OR ...)` trinary lets one statement cover both filtered
101/// and unfiltered cases. Matview names are unique per schema (Postgres enforces
102/// this via `pg_class`'s unique index on `(relname, relnamespace)`), so
103/// `matviewname` alone is a stable sort key — no tiebreaker needed.
104const BRIEF_SQL: &str = r"
105    SELECT matviewname
106    FROM pg_matviews
107    WHERE schemaname = 'public'
108      AND ($1::text IS NULL OR matviewname ILIKE '%' || $1 || '%')
109    ORDER BY matviewname
110    LIMIT $2 OFFSET $3";
111
112/// Detailed-mode SQL: per-matview `json_build_object` projection.
113///
114/// `pg_matviews` already filters to `relkind = 'm'`. The `pg_namespace` +
115/// `pg_class` joins anchor the relation OID needed by `obj_description`.
116/// Postgres defers SELECT-list evaluation past `LIMIT`, so `obj_description`
117/// only runs for the page's rows — never the full schema.
118/// `pg_matviews.matviewowner` is already a role name, so no `pg_roles` join is
119/// needed. `populated` and `indexed` are projected directly from
120/// `pg_matviews.ispopulated` / `pg_matviews.hasindexes`. Matview names are
121/// unique per schema, so `matviewname` alone is a stable sort key.
122const DETAILED_SQL: &str = r"
123    SELECT
124        mv.matviewname AS name,
125        json_build_object(
126            'schema',      mv.schemaname,
127            'owner',       mv.matviewowner,
128            'description', pg_catalog.obj_description(c.oid, 'pg_class'),
129            'definition',  mv.definition,
130            'populated',   mv.ispopulated,
131            'indexed',     mv.hasindexes
132        ) AS entry
133    FROM pg_matviews mv
134    JOIN pg_namespace n ON n.nspname = mv.schemaname
135    JOIN pg_class     c ON c.relname = mv.matviewname AND c.relnamespace = n.oid
136    WHERE mv.schemaname = 'public'
137      AND ($1::text IS NULL OR mv.matviewname ILIKE '%' || $1 || '%')
138    ORDER BY mv.matviewname
139    LIMIT $2 OFFSET $3";
140
141impl PostgresHandler {
142    /// Lists one page of user-defined materialized views, optionally filtered and/or detailed.
143    ///
144    /// # Errors
145    ///
146    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
147    /// or an internal-error [`ErrorData`] if `database` is invalid
148    /// or the underlying query fails.
149    pub async fn list_materialized_views(
150        &self,
151        ListMaterializedViewsRequest {
152            database,
153            cursor,
154            search,
155            detailed,
156        }: ListMaterializedViewsRequest,
157    ) -> Result<ListMaterializedViewsResponse, ErrorData> {
158        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
159        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
160        let pager = Pager::new(cursor, self.config.page_size);
161
162        if detailed {
163            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
164                .connection
165                .fetch(
166                    sqlx::query(DETAILED_SQL)
167                        .bind(pattern)
168                        .bind(pager.limit())
169                        .bind(pager.offset()),
170                    database,
171                )
172                .await?;
173            let (rows, next_cursor) = pager.paginate(rows);
174            return Ok(ListMaterializedViewsResponse::detailed(
175                rows.into_iter().map(|(key, json)| (key, json.0)).collect(),
176                next_cursor,
177            ));
178        }
179
180        let rows: Vec<String> = self
181            .connection
182            .fetch_scalar(
183                sqlx::query(BRIEF_SQL)
184                    .bind(pattern)
185                    .bind(pager.limit())
186                    .bind(pager.offset()),
187                database,
188            )
189            .await?;
190        let (materialized_views, next_cursor) = pager.paginate(rows);
191        Ok(ListMaterializedViewsResponse::brief(materialized_views, next_cursor))
192    }
193}
dbmcp_postgres/tools/list_materialized_views.rs

dbmcp_postgres/tools/
list_materialized_views.rs
