PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/functions.ts:794-907 (handler)

  Core handler logic for the 'pg_manage_functions' tool. Dispatches to internal _getFunctions, _createFunction, or _dropFunction based on the 'operation' parameter ('get', 'create', or 'drop'). Handles validation, connection resolution, and error formatting.
  execute: async (args: any, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => { const { connectionString: connStringArg, operation, functionName, schema, parameters, returnType, functionBody, language, volatility, security, replace, ifExists, cascade } = args as { connectionString?: string; operation: 'get' | 'create' | 'drop'; functionName?: string; schema?: string; parameters?: string; returnType?: string; functionBody?: string; language?: 'sql' | 'plpgsql' | 'plpython3u'; volatility?: 'VOLATILE' | 'STABLE' | 'IMMUTABLE'; security?: 'INVOKER' | 'DEFINER'; replace?: boolean; ifExists?: boolean; cascade?: boolean; }; const resolvedConnString = getConnectionStringVal(connStringArg); let result: FunctionResult; try { switch (operation) { case 'get': result = await _getFunctions(resolvedConnString, functionName, schema); if (result.success) { return { content: [{ type: 'text', text: JSON.stringify(result.details, null, 2) || result.message }] }; } break; case 'create': { // Debug logging to understand what's being passed console.error('DEBUG - Create operation parameters:', { functionName: functionName, parameters: parameters, returnType: returnType, functionBody: functionBody, parametersType: typeof parameters, parametersUndefined: parameters === undefined, parametersNull: parameters === null }); // Fix validation: be more specific about which fields are missing const missingFields = []; if (!functionName) missingFields.push('functionName'); if (!returnType) missingFields.push('returnType'); if (!functionBody) missingFields.push('functionBody'); if (missingFields.length > 0) { return { content: [{ type: 'text', text: `Error: Missing required fields: ${missingFields.join(', ')}. Note: parameters can be empty string "" for functions with no parameters` }], isError: true }; } // Normalize parameters: treat undefined, null, or whitespace-only as empty string const normalizedParameters: string = parameters === undefined || parameters === null ? '' : (typeof parameters === 'string' && parameters.trim() === '') ? '' : String(parameters); result = await _createFunction(resolvedConnString, functionName as string, normalizedParameters, returnType as string, functionBody as string, { language, volatility, schema, security, replace }); break; } case 'drop': if (!functionName) { return { content: [{ type: 'text', text: 'Error: functionName is required for drop operation' }], isError: true }; } result = await _dropFunction(resolvedConnString, functionName, parameters, { schema, ifExists, cascade }); break; default: return { content: [{ type: 'text', text: `Error: Unknown operation "${operation}". Supported operations: get, create, drop` }], isError: true }; } if (result.success) { return { content: [{ type: 'text', text: result.message + (result.details ? ` Details: ${JSON.stringify(result.details)}` : '') }] }; } return { content: [{ type: 'text', text: result.message }], isError: true }; } catch (error) { return { content: [{ type: 'text', text: `Error executing ${operation} operation: ${error instanceof Error ? error.message : String(error)}` }], isError: true }; } }
• src/tools/functions.ts:772-792 (schema)

  Zod input schema defining parameters for pg_manage_functions tool, including operation type and specific fields for create/drop operations.
  inputSchema: z.object({ connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'), operation: z.enum(['get', 'create', 'drop']).describe('Operation to perform: get (list/info), create (new function), or drop (remove function)'), // Common parameters functionName: z.string().optional().describe('Name of the function (required for create/drop, optional for get to filter)'), schema: z.string().optional().describe('Schema name (defaults to public)'), // Create operation parameters parameters: z.string().optional().describe('Function parameters - required for create operation, required for drop when function is overloaded. Use empty string "" for functions with no parameters'), returnType: z.string().optional().describe('Return type of the function (required for create operation)'), functionBody: z.string().optional().describe('Function body code (required for create operation)'), language: z.enum(['sql', 'plpgsql', 'plpython3u']).optional().describe('Function language (defaults to plpgsql for create)'), volatility: z.enum(['VOLATILE', 'STABLE', 'IMMUTABLE']).optional().describe('Function volatility (defaults to VOLATILE for create)'), security: z.enum(['INVOKER', 'DEFINER']).optional().describe('Function security context (defaults to INVOKER for create)'), replace: z.boolean().optional().describe('Whether to replace the function if it exists (for create operation)'), // Drop operation parameters ifExists: z.boolean().optional().describe('Whether to include IF EXISTS clause (for drop operation)'), cascade: z.boolean().optional().describe('Whether to include CASCADE clause (for drop operation)') }),
• src/tools/functions.ts:769-771 (registration)

  Tool registration exporting the pg_manage_functions tool object with name, description, schema, and execute handler.
  export const manageFunctionsTool: PostgresTool = { name: 'pg_manage_functions', description: 'Manage PostgreSQL functions - get, create, or drop functions with a single tool. Examples: operation="get" to list functions, operation="create" with functionName="test_func", parameters="" (empty for no params), returnType="TEXT", functionBody="SELECT \'Hello\'"',
• src/tools/functions.ts:24-81 (helper)

  Helper function to retrieve information about database functions using PostgreSQL system catalogs.
  async function _getFunctions( connectionString: string, functionName?: string, schema = 'public' ): Promise<FunctionResult> { const db = DatabaseConnection.getInstance(); try { await db.connect(connectionString); let query = ` SELECT p.proname AS name, l.lanname AS language, pg_get_function_result(p.oid) AS "returnType", pg_get_function_arguments(p.oid) AS "arguments", CASE WHEN p.provolatile = 'i' THEN 'IMMUTABLE' WHEN p.provolatile = 's' THEN 'STABLE' WHEN p.provolatile = 'v' THEN 'VOLATILE' END AS volatility, pg_get_functiondef(p.oid) AS definition, a.rolname AS owner FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid JOIN pg_language l ON p.prolang = l.oid JOIN pg_authid a ON p.proowner = a.oid WHERE n.nspname = $1 `; const params: (string | undefined)[] = [schema]; if (functionName) { query += ' AND p.proname = $2'; params.push(functionName); } query += ' ORDER BY p.proname'; const functions = await db.query<FunctionInfo>(query, params); return { success: true, message: functionName ? `Function information for ${functionName}` : `Found ${functions.length} functions in schema ${schema}`, details: functions }; } catch (error) { return { success: false, message: `Failed to get function information: ${error instanceof Error ? error.message : String(error)}`, details: null }; } finally { await db.disconnect(); } }
• src/tools/functions.ts:86-146 (helper)

  Helper function to create or replace a database function with specified parameters and body.
  async function _createFunction( connectionString: string, functionName: string, parameters: string, returnType: string, functionBody: string, options: { language?: 'sql' | 'plpgsql' | 'plpython3u'; volatility?: 'VOLATILE' | 'STABLE' | 'IMMUTABLE'; schema?: string; security?: 'INVOKER' | 'DEFINER'; replace?: boolean; } = {} ): Promise<FunctionResult> { const db = DatabaseConnection.getInstance(); try { await db.connect(connectionString); const language = options.language || 'plpgsql'; const volatility = options.volatility || 'VOLATILE'; const schema = options.schema || 'public'; const security = options.security || 'INVOKER'; const createOrReplace = options.replace ? 'CREATE OR REPLACE' : 'CREATE'; // Build function creation SQL const sql = ` ${createOrReplace} FUNCTION ${schema}.${functionName}(${parameters}) RETURNS ${returnType} LANGUAGE ${language} ${volatility} SECURITY ${security} AS $function$ ${functionBody} $function$; `; await db.query(sql); return { success: true, message: `Function ${functionName} created successfully`, details: { name: functionName, schema, returnType, language, volatility, security } }; } catch (error) { return { success: false, message: `Failed to create function: ${error instanceof Error ? error.message : String(error)}`, details: null }; } finally { await db.disconnect(); } }
• src/tools/functions.ts:151-202 (helper)

  Helper function to drop a database function, supporting overloaded functions and CASCADE.
  async function _dropFunction( connectionString: string, functionName: string, parameters?: string, options: { schema?: string; ifExists?: boolean; cascade?: boolean; } = {} ): Promise<FunctionResult> { const db = DatabaseConnection.getInstance(); try { await db.connect(connectionString); const schema = options.schema || 'public'; const ifExists = options.ifExists ? 'IF EXISTS' : ''; const cascade = options.cascade ? 'CASCADE' : ''; // Build function drop SQL let sql = `DROP FUNCTION ${ifExists} ${schema}.${functionName}`; // Add parameters if provided if (parameters) { sql += `(${parameters})`; } // Add cascade if specified if (cascade) { sql += ` ${cascade}`; } await db.query(sql); return { success: true, message: `Function ${functionName} dropped successfully`, details: { name: functionName, schema } }; } catch (error) { return { success: false, message: `Failed to drop function: ${error instanceof Error ? error.message : String(error)}`, details: null }; } finally { await db.disconnect(); } }