dbmcp_postgres/tools/
list_functions.rs

1//! MCP tool: `listFunctions`.
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::{ListFunctionsRequest, ListFunctionsResponse};
12
13/// Marker type for the `listFunctions` MCP tool.
14pub(crate) struct ListFunctionsTool;
15
16impl ListFunctionsTool {
17    const NAME: &'static str = "listFunctions";
18    const TITLE: &'static str = "List Functions";
19    const DESCRIPTION: &'static str = r#"List user-defined functions in the `public` schema, optionally filtered and/or with full metadata. Aggregates, window functions, and procedures are excluded.
20
21<usecase>
22Use when:
23- Auditing functions across a database (brief mode, default).
24- Searching for a function by partial name (pass `search`).
25- Inspecting a function's language, signature, return type, volatility, strictness, security mode, parallel-safety, owner, comment, and full `CREATE FUNCTION` text before reasoning about correctness or invocation safety (pass `detailed: true`). Detailed mode supersedes ad-hoc `readQuery` against `pg_proc` / `information_schema.routines`.
26</usecase>
27
28<parameters>
29- `database` — Database to target. Defaults to the active database.
30- `cursor` — Opaque pagination cursor; echo the prior response's `nextCursor`.
31- `search` — Case-insensitive filter on function names via `ILIKE`. `%` matches any sequence; `_` matches a single character.
32- `detailed` — When `true`, returns full metadata objects keyed by `name(arguments)` instead of bare name strings. Default `false`.
33</parameters>
34
35<examples>
36✓ "What functions are in mydb?" → listFunctions(database="mydb")
37✓ "Find the order-total calculation" → listFunctions(search="order")
38✓ "What does calc_order_total do?" → listFunctions(search="calc_order_total", detailed=true)
39✗ "List stored procedures" → use listProcedures instead
40✗ "List aggregates" → not supported; aggregates are excluded
41</examples>
42
43<what_it_returns>
44Brief mode (default): a sorted JSON array of function-name strings, e.g. `["audit_user_login", "calc_order_subtotal", "calc_order_total", "calc_order_total"]`. Overloaded functions appear as one entry per overload (duplicate name strings allowed).
45Detailed mode: a JSON object keyed by function signature `name(arguments)`; each value carries `schema`, `name`, `language`, `arguments`, `returnType`, `volatility` (IMMUTABLE/STABLE/VOLATILE), `strict` (boolean), `security` (INVOKER/DEFINER), `parallelSafety` (SAFE/RESTRICTED/UNSAFE), `owner`, `description` (or null when no `COMMENT ON FUNCTION`), and `definition` (the full `CREATE OR REPLACE FUNCTION` text). Overloads occupy distinct keys (e.g. `calc_total(integer)` vs `calc_total(integer, numeric)`).
46</what_it_returns>
47
48<pagination>
49Paginated. Pass the prior response's `nextCursor` as `cursor` to fetch the next page. The `search` filter must stay the same across pages for cursor continuity. Brief and detailed modes share the same `(proname, oid)` row order, so a client can switch `detailed` between pages without losing position.
50</pagination>"#;
51}
52
53impl ToolBase for ListFunctionsTool {
54    type Parameter = ListFunctionsRequest;
55    type Output = ListFunctionsResponse;
56    type Error = ErrorData;
57
58    fn name() -> Cow<'static, str> {
59        Self::NAME.into()
60    }
61
62    fn title() -> Option<String> {
63        Some(Self::TITLE.into())
64    }
65
66    fn description() -> Option<Cow<'static, str>> {
67        Some(Self::DESCRIPTION.into())
68    }
69
70    fn annotations() -> Option<ToolAnnotations> {
71        Some(
72            ToolAnnotations::new()
73                .read_only(true)
74                .destructive(false)
75                .idempotent(true)
76                .open_world(false),
77        )
78    }
79}
80
81impl AsyncTool<PostgresHandler> for ListFunctionsTool {
82    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
83        handler.list_functions(params).await
84    }
85}
86
87/// Brief-mode SQL: `pg_proc` scan with `ILIKE` filter on function name.
88///
89/// `n.nspname = 'public'` plus `p.prokind = 'f'` keep aggregates / window
90/// functions / procedures out of the result. The `($1::text IS NULL OR ...)`
91/// trinary lets one statement cover both filtered and unfiltered cases.
92/// `(p.proname, p.oid)` is the sort key — `oid` is the unique tiebreaker
93/// across overloaded names so `OFFSET` pagination is deterministic.
94const BRIEF_SQL: &str = r"
95    SELECT p.proname
96    FROM pg_proc p
97    JOIN pg_namespace n ON n.oid = p.pronamespace
98    WHERE n.nspname = 'public'
99      AND p.prokind = 'f'
100      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
101    ORDER BY p.proname, p.oid
102    LIMIT $2 OFFSET $3";
103
104/// Detailed-mode SQL: per-function `json_build_object` projection.
105///
106/// `n.nspname = 'public'` and `p.prokind = 'f'` filter to user-defined
107/// functions in the `public` schema. Three small lookup joins (`pg_namespace`,
108/// `pg_language`, `pg_roles`) supply the language and owner names. Postgres
109/// defers SELECT-list evaluation past `LIMIT`, so the expensive `pg_get_*`
110/// projections (`pg_get_functiondef`, `pg_get_function_arguments`,
111/// `pg_get_function_result`) and `obj_description` only run for the page's
112/// rows — never the full schema.
113///
114/// A `CROSS JOIN LATERAL` materialises `pg_get_function_arguments(p.oid)`
115/// into `args.text` so it is computed once per row and reused both in the
116/// keyed signature `name(args)` and in the JSON `arguments` field — no
117/// double-call, no Rust-side json lookup.
118///
119/// `provolatile`, `proisstrict`, `prosecdef`, `proparallel` are stable
120/// `pg_proc` columns. `provolatile` is `'i'`/`'s'`/`'v'`; `proparallel` is
121/// `'s'`/`'r'`/`'u'`; `prosecdef` is a boolean.
122const DETAILED_SQL: &str = r"
123    SELECT
124        p.proname || '(' || args.text || ')' AS name,
125        json_build_object(
126            'schema',         'public',
127            'name',           p.proname,
128            'language',       l.lanname,
129            'arguments',      args.text,
130            'returnType',     pg_get_function_result(p.oid),
131            'volatility',     CASE p.provolatile
132                                  WHEN 'i' THEN 'IMMUTABLE'
133                                  WHEN 's' THEN 'STABLE'
134                                  WHEN 'v' THEN 'VOLATILE'
135                              END,
136            'strict',         p.proisstrict,
137            'security',       CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END,
138            'parallelSafety', CASE p.proparallel
139                                  WHEN 's' THEN 'SAFE'
140                                  WHEN 'r' THEN 'RESTRICTED'
141                                  WHEN 'u' THEN 'UNSAFE'
142                              END,
143            'owner',          r.rolname,
144            'description',    pg_catalog.obj_description(p.oid, 'pg_proc'),
145            'definition',     pg_get_functiondef(p.oid)
146        ) AS entry
147    FROM pg_proc p
148    JOIN pg_namespace n ON n.oid = p.pronamespace
149    JOIN pg_language  l ON l.oid = p.prolang
150    JOIN pg_roles     r ON r.oid = p.proowner
151    CROSS JOIN LATERAL (SELECT pg_get_function_arguments(p.oid) AS text) args
152    WHERE n.nspname = 'public'
153      AND p.prokind = 'f'
154      AND ($1::text IS NULL OR p.proname ILIKE '%' || $1 || '%')
155    ORDER BY p.proname, p.oid
156    LIMIT $2 OFFSET $3";
157
158impl PostgresHandler {
159    /// Lists one page of user-defined functions, optionally filtered and/or detailed.
160    ///
161    /// # Errors
162    ///
163    /// Returns [`ErrorData`] with code `-32602` if `cursor` is malformed,
164    /// or an internal-error [`ErrorData`] if `database` is invalid
165    /// or the underlying query fails.
166    pub async fn list_functions(
167        &self,
168        ListFunctionsRequest {
169            database,
170            cursor,
171            search,
172            detailed,
173        }: ListFunctionsRequest,
174    ) -> Result<ListFunctionsResponse, ErrorData> {
175        let database = database.as_deref().map(str::trim).filter(|s| !s.is_empty());
176        let pattern = search.as_deref().map(str::trim).filter(|s| !s.is_empty());
177        let pager = Pager::new(cursor, self.config.page_size);
178
179        if detailed {
180            let rows: Vec<(String, sqlx::types::Json<serde_json::Value>)> = self
181                .connection
182                .fetch(
183                    sqlx::query(DETAILED_SQL)
184                        .bind(pattern)
185                        .bind(pager.limit())
186                        .bind(pager.offset()),
187                    database,
188                )
189                .await?;
190            let (rows, next_cursor) = pager.paginate(rows);
191            return Ok(ListFunctionsResponse::detailed(
192                rows.into_iter().map(|(key, json)| (key, json.0)).collect(),
193                next_cursor,
194            ));
195        }
196
197        let rows: Vec<String> = self
198            .connection
199            .fetch_scalar(
200                sqlx::query(BRIEF_SQL)
201                    .bind(pattern)
202                    .bind(pager.limit())
203                    .bind(pager.offset()),
204                database,
205            )
206            .await?;
207        let (functions, next_cursor) = pager.paginate(rows);
208        Ok(ListFunctionsResponse::brief(functions, next_cursor))
209    }
210}
dbmcp_postgres/tools/list_functions.rs

dbmcp_postgres/tools/
list_functions.rs
