dbmcp_postgres/tools/
list_procedures.rs

1//! MCP tool: `listProcedures`.
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::{ListProceduresRequest, ListProceduresResponse};
12
13/// Marker type for the `listProcedures` MCP tool.
14pub(crate) struct ListProceduresTool;
15
16impl ListProceduresTool {
17    const NAME: &'static str = "listProcedures";
18    const TITLE: &'static str = "List Procedures";
19    const DESCRIPTION: &'static str = r#"List user-defined procedures in the `public` schema, optionally filtered and/or with full metadata. Functions, aggregates, and window functions are excluded.
20
21<usecase>
22Use when:
23- Auditing procedures across a database (brief mode, default).
24- Searching for a procedure by partial name (pass `search`).
25- Inspecting a procedure's language, signature, security mode, owner, comment, and full `CREATE PROCEDURE` text before reasoning about correctness or invocation safety (pass `detailed: true`). Detailed mode supersedes ad-hoc `readQuery` against `pg_proc` / `information_schema.routines`.
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 procedure names via `ILIKE`. `%` matches any sequence; `_` matches a single character.
32- `detailed` — When `true`, returns full metadata objects keyed by `name(arguments)` instead of bare name strings. Default `false`.
33</parameters>
34
35<examples>
36✓ "What procedures are in mydb?" → listProcedures(database="mydb")
37✓ "Find the order archival routine" → listProcedures(search="archive")
38✓ "What does archive_order do?" → listProcedures(search="archive_order", detailed=true)
39✗ "List functions" → use listFunctions instead
40✗ "List aggregates" → not supported; aggregates are excluded
41</examples>
42
43<what_it_returns>
44Brief mode (default): a sorted JSON array of procedure-name strings, e.g. `["archive_order", "archive_order_history", "archive_order_history"]`. Overloaded procedures appear as one entry per overload (duplicate name strings allowed).
45Detailed mode: a JSON object keyed by procedure signature `name(arguments)`; each value carries `schema`, `name`, `language`, `arguments`, `security` (INVOKER/DEFINER), `owner`, `description` (or null when no `COMMENT ON PROCEDURE`), and `definition` (the full `CREATE OR REPLACE PROCEDURE` text). Overloads occupy distinct keys (e.g. `archive_order_history(integer)` vs `archive_order_history(integer, boolean)`). Zero-arg procedures key as `name()` — the parens are always present so the key shape stays uniform.
46
47Detailed mode deliberately omits the `listFunctions`-only fields `returnType`, `volatility`, `strict`, and `parallelSafety`: procedures don't return a value, `pg_proc.provolatile` / `proisstrict` are not user-settable for procedures, and `proparallel` carries no procedure-level guarantee in PostgreSQL.
48</what_it_returns>
49
50<pagination>
51Paginated. 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 `(proname, oid)` row order, so a client can switch `detailed` between pages without losing position.
52</pagination>"#;
53}
54
55impl ToolBase for ListProceduresTool {
56    type Parameter = ListProceduresRequest;
57    type Output = ListProceduresResponse;
58    type Error = ErrorData;
59
60    fn name() -> Cow<'static, str> {
61        Self::NAME.into()
62    }
63
64    fn title() -> Option<String> {
65        Some(Self::TITLE.into())
66    }
67
68    fn description() -> Option<Cow<'static, str>> {
69        Some(Self::DESCRIPTION.into())
70    }
71
72    fn annotations() -> Option<ToolAnnotations> {
73        Some(
74            ToolAnnotations::new()
75                .read_only(true)
76                .destructive(false)
77                .idempotent(true)
78                .open_world(false),
79        )
80    }
81}
82
83impl AsyncTool<PostgresHandler> for ListProceduresTool {
84    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
85        handler.list_procedures(params).await
86    }
87}
88
89/// Brief-mode SQL: `pg_proc` scan with `ILIKE` filter on procedure name.
90///
91/// `n.nspname = 'public'` plus `p.prokind = 'p'` keep functions / aggregates /
92/// window functions out of the result. The `($1::text IS NULL OR ...)` trinary
93/// lets one statement cover both filtered and unfiltered cases. `(p.proname,
94/// p.oid)` is the sort key — `oid` is the unique tiebreaker across overloaded
95/// names so `OFFSET` pagination is deterministic.
96const BRIEF_SQL: &str = r"
97    SELECT p.proname
98    FROM pg_proc p
99    JOIN pg_namespace n ON n.oid = p.pronamespace
100    WHERE n.nspname = 'public'
101      AND p.prokind = 'p'
102      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
103    ORDER BY p.proname, p.oid
104    LIMIT $2 OFFSET $3";
105
106/// Detailed-mode SQL: per-procedure `json_build_object` projection.
107///
108/// `n.nspname = 'public'` and `p.prokind = 'p'` filter to user-defined
109/// procedures in the `public` schema. Three small lookup joins (`pg_namespace`,
110/// `pg_language`, `pg_roles`) supply the language and owner names. Postgres
111/// defers SELECT-list evaluation past `LIMIT`, so the expensive `pg_get_*`
112/// projections (`pg_get_functiondef`, `pg_get_function_arguments`) and
113/// `obj_description` only run for the page's rows — never the full schema.
114///
115/// A `CROSS JOIN LATERAL` materialises `pg_get_function_arguments(p.oid)` into
116/// `args.text` so it is computed once per row and reused both in the keyed
117/// signature `name(args)` and in the JSON `arguments` field — no double-call.
118///
119/// `prosecdef` is a boolean. Procedure-only fields (`returnType`, `volatility`,
120/// `strict`, `parallelSafety`) are deliberately absent — see DESCRIPTION above.
121const DETAILED_SQL: &str = r"
122    SELECT
123        p.proname || '(' || args.text || ')' AS name,
124        json_build_object(
125            'schema',      'public',
126            'name',        p.proname,
127            'language',    l.lanname,
128            'arguments',   args.text,
129            'security',    CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END,
130            'owner',       r.rolname,
131            'description', pg_catalog.obj_description(p.oid, 'pg_proc'),
132            'definition',  pg_get_functiondef(p.oid)
133        ) AS entry
134    FROM pg_proc p
135    JOIN pg_namespace n ON n.oid = p.pronamespace
136    JOIN pg_language  l ON l.oid = p.prolang
137    JOIN pg_roles     r ON r.oid = p.proowner
138    CROSS JOIN LATERAL (SELECT pg_get_function_arguments(p.oid) AS text) args
139    WHERE n.nspname = 'public'
140      AND p.prokind = 'p'
141      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
142    ORDER BY p.proname, p.oid
143    LIMIT $2 OFFSET $3";
144
145impl PostgresHandler {
146    /// Lists one page of user-defined procedures, optionally filtered and/or detailed.
147    ///
148    /// # Errors
149    ///
150    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
151    /// or an internal-error [`ErrorData`] if `database` is invalid
152    /// or the underlying query fails.
153    pub async fn list_procedures(
154        &self,
155        ListProceduresRequest {
156            database,
157            cursor,
158            search,
159            detailed,
160        }: ListProceduresRequest,
161    ) -> Result<ListProceduresResponse, ErrorData> {
162        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
163        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
164        let pager = Pager::new(cursor, self.config.page_size);
165
166        if detailed {
167            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
168                .connection
169                .fetch(
170                    sqlx::query(DETAILED_SQL)
171                        .bind(pattern)
172                        .bind(pager.limit())
173                        .bind(pager.offset()),
174                    database,
175                )
176                .await?;
177            let (rows, next_cursor) = pager.paginate(rows);
178            return Ok(ListProceduresResponse::detailed(
179                rows.into_iter().map(|(key, json)| (key, json.0)).collect(),
180                next_cursor,
181            ));
182        }
183
184        let rows: Vec<String> = self
185            .connection
186            .fetch_scalar(
187                sqlx::query(BRIEF_SQL)
188                    .bind(pattern)
189                    .bind(pager.limit())
190                    .bind(pager.offset()),
191                database,
192            )
193            .await?;
194        let (procedures, next_cursor) = pager.paginate(rows);
195        Ok(ListProceduresResponse::brief(procedures, next_cursor))
196    }
197}
dbmcp_postgres/tools/list_procedures.rs

dbmcp_postgres/tools/
list_procedures.rs
