postgres-mcp

Instructions

Args:
    schema: Schema name (default: public)
    
Returns:
    List of functions with name, arguments, and return type

Implementation Reference

• postgres_mcp/server.py:356-374 (handler)

  MCP tool handler and registration for 'list_functions'. This decorated function handles tool execution, delegates to PostgresClient, and formats output using FunctionSummary models.

  @mcp.tool()
  @handle_db_error
  def list_functions(schema: str = "public") -> dict:
      """List all functions and procedures in a schema.
      
      Args:
          schema: Schema name (default: public)
          
      Returns:
          List of functions with name, arguments, and return type
      """
      client = get_client()
      functions = client.list_functions(schema)
      
      return {
          "schema": schema,
          "functions": [FunctionSummary.from_row(f).model_dump() for f in functions],
      }

• postgres_mcp/postgres_client.py:436-467 (helper)

  Core implementation in PostgresClient that executes the SQL query against pg_proc to retrieve function metadata from the database.

  def list_functions(self, schema: str = "public") -> list[dict]:
      """List functions and procedures in a schema.
      
      Args:
          schema: Schema name
          
      Returns:
          List of function dicts
      """
      query = """
          SELECT 
              p.proname AS routine_name,
              n.nspname AS routine_schema,
              pg_get_function_result(p.oid) AS return_type,
              pg_get_function_arguments(p.oid) AS argument_types,
              CASE p.prokind
                  WHEN 'f' THEN 'function'
                  WHEN 'p' THEN 'procedure'
                  WHEN 'a' THEN 'aggregate'
                  WHEN 'w' THEN 'window'
                  ELSE 'unknown'
              END AS routine_type
          FROM pg_proc p
          JOIN pg_namespace n ON n.oid = p.pronamespace
          WHERE n.nspname = %s
              AND p.proname NOT LIKE 'pg_%'
          ORDER BY p.proname
      """
      with self.get_cursor() as cursor:
          cursor.execute(query, (schema,))
          return [dict(row) for row in cursor.fetchall()]

• postgres_mcp/models.py:183-201 (schema)

  Pydantic model defining the output schema for function summaries, used in the tool response for type validation and serialization.

  class FunctionSummary(BaseModel):
      """Function/procedure info."""
      
      name: str
      schema_name: str = "public"
      return_type: Optional[str] = None
      argument_types: str = ""
      func_type: str = "function"  # function, procedure, aggregate
      
      @classmethod
      def from_row(cls, row: dict) -> "FunctionSummary":
          return cls(
              name=row.get("routine_name", row.get("proname", "")),
              schema_name=row.get("routine_schema", row.get("nspname", "public")),
              return_type=row.get("data_type", row.get("return_type")),
              argument_types=row.get("argument_types", ""),
              func_type=row.get("routine_type", "function").lower(),
          )