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

dbmcp_postgres/tools/
list_procedures.rs
