dbmcp_postgres/tools/
list_procedures.rs

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

dbmcp_postgres/tools/
list_procedures.rs
