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();
    }
  }