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 an internal-error [`ErrorData`] if `database` is invalid
180    /// or the underlying query fails.
181    pub async fn list_functions(
182        &self,
183        database: Option<String>,
184        cursor: Option<Cursor>,
185        search: Option<String>,
186        detailed: bool,
187    ) -> Result<ListEntriesResponse, ErrorData> {
188        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
189        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
190        let pager = Pager::new(cursor, self.config.page_size);
191
192        if detailed {
193            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
194                .connection
195                .fetch(
196                    sqlx::query(DETAILED_SQL)
197                        .bind(pattern)
198                        .bind(pager.limit())
199                        .bind(pager.offset()),
200                    database,
201                )
202                .await?;
203            let (rows, next_cursor) = pager.paginate(rows);
204            return Ok(ListEntriesResponse::detailed(
205                rows.into_iter().map(|(key, json)| (key, json.0)).collect(),
206                next_cursor,
207            ));
208        }
209
210        let rows: Vec<String> = self
211            .connection
212            .fetch_scalar(
213                sqlx::query(BRIEF_SQL)
214                    .bind(pattern)
215                    .bind(pager.limit())
216                    .bind(pager.offset()),
217                database,
218            )
219            .await?;
220        let (functions, next_cursor) = pager.paginate(rows);
221        Ok(ListEntriesResponse::brief(functions, next_cursor))
222    }
223}
dbmcp_postgres/tools/list_functions.rs

dbmcp_postgres/tools/
list_functions.rs
