dbmcp_postgres/tools/
list_functions.rs

1//! MCP tool: `listFunctions`.
2
3use dbmcp_server::pagination::{Cursor, Pager};
4
5use super::prelude::*;
6use crate::types::{ListEntriesResponse, ListFunctionsRequest};
7
8const NAME: &str = "listFunctions";
9const TITLE: &str = "List Functions";
10const DESCRIPTION_PINNED: &str = include_str!("../../assets/tools/list_functions/pinned.md");
11const DESCRIPTION_UNPINNED: &str = include_str!("../../assets/tools/list_functions/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 `listFunctions` MCP tool (pinned variant — no `database` field).
22pub(crate) struct PinnedListFunctionsTool;
23
24impl ToolBase for PinnedListFunctionsTool {
25    type Parameter = ListFunctionsRequest;
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 PinnedListFunctionsTool {
55    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
56        handler
57            .list_functions(None, params.cursor, params.search, params.detailed)
58            .await
59    }
60}
61
62/// Marker type for the `listFunctions` MCP tool (unpinned variant — carries `database`).
63pub(crate) struct UnpinnedListFunctionsTool;
64
65impl ToolBase for UnpinnedListFunctionsTool {
66    type Parameter = ListFunctionsRequest;
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 UnpinnedListFunctionsTool {
96    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
97        handler
98            .list_functions(params.database, params.cursor, params.search, params.detailed)
99            .await
100    }
101}
102
103/// Brief-mode SQL: `pg_proc` scan with `ILIKE` filter on function name.
104///
105/// `n.nspname = 'public'` plus `p.prokind = 'f'` keep aggregates / window
106/// functions / procedures out of the result. The `($1::text IS NULL OR ...)`
107/// trinary lets one statement cover both filtered and unfiltered cases.
108/// `(p.proname, p.oid)` is the sort key — `oid` is the unique tiebreaker
109/// across overloaded 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 = 'f'
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-function `json_build_object` projection.
121///
122/// `n.nspname = 'public'` and `p.prokind = 'f'` filter to user-defined
123/// functions 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`,
127/// `pg_get_function_result`) and `obj_description` only run for the page's
128/// rows — never the full schema.
129///
130/// A `CROSS JOIN LATERAL` materialises `pg_get_function_arguments(p.oid)`
131/// into `args.text` so it is computed once per row and reused both in the
132/// keyed signature `name(args)` and in the JSON `arguments` field — no
133/// double-call, no Rust-side json lookup.
134///
135/// `provolatile`, `proisstrict`, `prosecdef`, `proparallel` are stable
136/// `pg_proc` columns. `provolatile` is `'i'`/`'s'`/`'v'`; `proparallel` is
137/// `'s'`/`'r'`/`'u'`; `prosecdef` is a boolean.
138const DETAILED_SQL: &str = r"
139    SELECT
140        p.proname || '(' || args.text || ')' AS name,
141        json_build_object(
142            'schema',         'public',
143            'name',           p.proname,
144            'language',       l.lanname,
145            'arguments',      args.text,
146            'returnType',     pg_get_function_result(p.oid),
147            'volatility',     CASE p.provolatile
148                                  WHEN 'i' THEN 'IMMUTABLE'
149                                  WHEN 's' THEN 'STABLE'
150                                  WHEN 'v' THEN 'VOLATILE'
151                              END,
152            'strict',         p.proisstrict,
153            'security',       CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END,
154            'parallelSafety', CASE p.proparallel
155                                  WHEN 's' THEN 'SAFE'
156                                  WHEN 'r' THEN 'RESTRICTED'
157                                  WHEN 'u' THEN 'UNSAFE'
158                              END,
159            'owner',          r.rolname,
160            'description',    pg_catalog.obj_description(p.oid, 'pg_proc'),
161            'definition',     pg_get_functiondef(p.oid)
162        ) AS entry
163    FROM pg_proc p
164    JOIN pg_namespace n ON n.oid = p.pronamespace
165    JOIN pg_language  l ON l.oid = p.prolang
166    JOIN pg_roles     r ON r.oid = p.proowner
167    CROSS JOIN LATERAL (SELECT pg_get_function_arguments(p.oid) AS text) args
168    WHERE n.nspname = 'public'
169      AND p.prokind = 'f'
170      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
171    ORDER BY p.proname, p.oid
172    LIMIT $2 OFFSET $3";
173
174impl PostgresHandler {
175    /// Lists one page of user-defined functions, optionally filtered and/or detailed.
176    ///
177    /// # Errors
178    ///
179    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
180    /// or an internal-error [`ErrorData`] if `database` is invalid
181    /// or the underlying query fails.
182    pub async fn list_functions(
183        &self,
184        database: Option<String>,
185        cursor: Option<Cursor>,
186        search: Option<String>,
187        detailed: bool,
188    ) -> Result<ListEntriesResponse, ErrorData> {
189        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
190        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
191        let pager = Pager::new(cursor, self.config.page_size);
192
193        if detailed {
194            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
195                .connection
196                .fetch(
197                    sqlx::query(DETAILED_SQL)
198                        .bind(pattern)
199                        .bind(pager.limit())
200                        .bind(pager.offset()),
201                    database,
202                )
203                .await?;
204            let (rows, next_cursor) = pager.paginate(rows);
205            return Ok(ListEntriesResponse::detailed(
206                rows.into_iter().map(|(key, json)| (key, json.0)).collect(),
207                next_cursor,
208            ));
209        }
210
211        let rows: Vec<String> = self
212            .connection
213            .fetch_scalar(
214                sqlx::query(BRIEF_SQL)
215                    .bind(pattern)
216                    .bind(pager.limit())
217                    .bind(pager.offset()),
218                database,
219            )
220            .await?;
221        let (functions, next_cursor) = pager.paginate(rows);
222        Ok(ListEntriesResponse::brief(functions, next_cursor))
223    }
224}
dbmcp_postgres/tools/list_functions.rs

dbmcp_postgres/tools/
list_functions.rs
