dbmcp_postgres/tools/
list_procedures.rs

1//! MCP tool: `listProcedures`.
2
3use std::borrow::Cow;
4
5use dbmcp_server::pagination::{Cursor, 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::{ListProceduresResponse, PinnedListProceduresRequest, UnpinnedListProceduresRequest};
12
13const NAME: &str = "listProcedures";
14const TITLE: &str = "List Procedures";
15const DESCRIPTION_PINNED: &str = include_str!("../../assets/tools/list_procedures/pinned.md");
16const DESCRIPTION_UNPINNED: &str = include_str!("../../assets/tools/list_procedures/unpinned.md");
17
18fn annotations() -> ToolAnnotations {
19    ToolAnnotations::new()
20        .read_only(true)
21        .destructive(false)
22        .idempotent(true)
23        .open_world(false)
24}
25
26/// Marker type for the `listProcedures` MCP tool (pinned variant — no `database` field).
27pub(crate) struct PinnedListProceduresTool;
28
29impl ToolBase for PinnedListProceduresTool {
30    type Parameter = PinnedListProceduresRequest;
31    type Output = ListProceduresResponse;
32    type Error = ErrorData;
33
34    fn name() -> Cow<'static, str> {
35        NAME.into()
36    }
37
38    fn title() -> Option<String> {
39        Some(TITLE.into())
40    }
41
42    fn description() -> Option<Cow<'static, str>> {
43        Some(DESCRIPTION_PINNED.into())
44    }
45
46    fn annotations() -> Option<ToolAnnotations> {
47        Some(annotations())
48    }
49}
50
51impl AsyncTool<PostgresHandler> for PinnedListProceduresTool {
52    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
53        handler
54            .list_procedures(None, params.cursor, params.search, params.detailed)
55            .await
56    }
57}
58
59/// Marker type for the `listProcedures` MCP tool (unpinned variant — carries `database`).
60pub(crate) struct UnpinnedListProceduresTool;
61
62impl ToolBase for UnpinnedListProceduresTool {
63    type Parameter = UnpinnedListProceduresRequest;
64    type Output = ListProceduresResponse;
65    type Error = ErrorData;
66
67    fn name() -> Cow<'static, str> {
68        NAME.into()
69    }
70
71    fn title() -> Option<String> {
72        Some(TITLE.into())
73    }
74
75    fn description() -> Option<Cow<'static, str>> {
76        Some(DESCRIPTION_UNPINNED.into())
77    }
78
79    fn annotations() -> Option<ToolAnnotations> {
80        Some(annotations())
81    }
82}
83
84impl AsyncTool<PostgresHandler> for UnpinnedListProceduresTool {
85    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
86        handler
87            .list_procedures(
88                params.database,
89                params.inner.cursor,
90                params.inner.search,
91                params.inner.detailed,
92            )
93            .await
94    }
95}
96
97/// Brief-mode SQL: `pg_proc` scan with `ILIKE` filter on procedure name.
98///
99/// `n.nspname = 'public'` plus `p.prokind = 'p'` keep functions / aggregates /
100/// window functions out of the result. The `($1::text IS NULL OR ...)` trinary
101/// lets one statement cover both filtered and unfiltered cases. `(p.proname,
102/// p.oid)` is the sort key — `oid` is the unique tiebreaker across overloaded
103/// names so `OFFSET` pagination is deterministic.
104const BRIEF_SQL: &str = r"
105    SELECT p.proname
106    FROM pg_proc p
107    JOIN pg_namespace n ON n.oid = p.pronamespace
108    WHERE n.nspname = 'public'
109      AND p.prokind = 'p'
110      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
111    ORDER BY p.proname, p.oid
112    LIMIT $2 OFFSET $3";
113
114/// Detailed-mode SQL: per-procedure `json_build_object` projection.
115///
116/// `n.nspname = 'public'` and `p.prokind = 'p'` filter to user-defined
117/// procedures in the `public` schema. Three small lookup joins (`pg_namespace`,
118/// `pg_language`, `pg_roles`) supply the language and owner names. Postgres
119/// defers SELECT-list evaluation past `LIMIT`, so the expensive `pg_get_*`
120/// projections (`pg_get_functiondef`, `pg_get_function_arguments`) and
121/// `obj_description` only run for the page's rows — never the full schema.
122///
123/// A `CROSS JOIN LATERAL` materialises `pg_get_function_arguments(p.oid)` into
124/// `args.text` so it is computed once per row and reused both in the keyed
125/// signature `name(args)` and in the JSON `arguments` field — no double-call.
126///
127/// `prosecdef` is a boolean. Procedure-only fields (`returnType`, `volatility`,
128/// `strict`, `parallelSafety`) are deliberately absent — see the tool description above.
129const DETAILED_SQL: &str = r"
130    SELECT
131        p.proname || '(' || args.text || ')' AS name,
132        json_build_object(
133            'schema',      'public',
134            'name',        p.proname,
135            'language',    l.lanname,
136            'arguments',   args.text,
137            'security',    CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END,
138            'owner',       r.rolname,
139            'description', pg_catalog.obj_description(p.oid, 'pg_proc'),
140            'definition',  pg_get_functiondef(p.oid)
141        ) AS entry
142    FROM pg_proc p
143    JOIN pg_namespace n ON n.oid = p.pronamespace
144    JOIN pg_language  l ON l.oid = p.prolang
145    JOIN pg_roles     r ON r.oid = p.proowner
146    CROSS JOIN LATERAL (SELECT pg_get_function_arguments(p.oid) AS text) args
147    WHERE n.nspname = 'public'
148      AND p.prokind = 'p'
149      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
150    ORDER BY p.proname, p.oid
151    LIMIT $2 OFFSET $3";
152
153impl PostgresHandler {
154    /// Lists one page of user-defined procedures, optionally filtered and/or detailed.
155    ///
156    /// # Errors
157    ///
158    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
159    /// or an internal-error [`ErrorData`] if `database` is invalid
160    /// or the underlying query fails.
161    pub async fn list_procedures(
162        &self,
163        database: Option<String>,
164        cursor: Option<Cursor>,
165        search: Option<String>,
166        detailed: bool,
167    ) -> Result<ListProceduresResponse, ErrorData> {
168        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
169        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
170        let pager = Pager::new(cursor, self.config.page_size);
171
172        if detailed {
173            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
174                .connection
175                .fetch(
176                    sqlx::query(DETAILED_SQL)
177                        .bind(pattern)
178                        .bind(pager.limit())
179                        .bind(pager.offset()),
180                    database,
181                )
182                .await?;
183            let (rows, next_cursor) = pager.paginate(rows);
184            return Ok(ListProceduresResponse::detailed(
185                rows.into_iter().map(|(key, json)| (key, json.0)).collect(),
186                next_cursor,
187            ));
188        }
189
190        let rows: Vec<String> = self
191            .connection
192            .fetch_scalar(
193                sqlx::query(BRIEF_SQL)
194                    .bind(pattern)
195                    .bind(pager.limit())
196                    .bind(pager.offset()),
197                database,
198            )
199            .await?;
200        let (procedures, next_cursor) = pager.paginate(rows);
201        Ok(ListProceduresResponse::brief(procedures, next_cursor))
202    }
203}
dbmcp_postgres/tools/list_procedures.rs

dbmcp_postgres/tools/
list_procedures.rs
