title: Splitting SQL result into groups with max size = n
body: <p>I have a table</p>
  
  <pre><code> id | volume_id| ... |
  ----+----------+-----+
    1 |       1  | ... |
    2 |       2  | ... |
    3 |       1  | ... |
    4 |       3  | ... |
    5 |       2  | ... |
    ...
  </code></pre>
  
  <p>I can do a simple grouping query:</p>
  
  <pre><code>select volume_id, count(*), min(id) as min_id, max(id) as max_id
  from my_table
  group by volume_id;
  </code></pre>
  
  <p>Which will produce result:</p>
  
  <pre><code> volume_id | count | min_id | max_id    
  -----------+-------+--------+--------
           1 | 67330 |  ...   | ...
           2 | 67330 |  ...   | ...
           3 | 67330 |  ...   | ...
           4 | 67330 |  ...   | ...
  </code></pre>
  
  <p>But I would like to split results into groups of 40K rows. So the results should look like:</p>
  
  <pre><code> volume_id | count | min_id | max_id    
  -----------+-------+--------+--------
           1 | 40000 |  ...   | ...      &lt;- first  group of IDs for volume 1
           1 | 27330 |  ...   | ...      &lt;- second group of IDs for volume 1
           2 | 40000 |  ...   | ...
           2 | 27330 |  ...   | ...
           3 | 40000 |  ...   | ...
           4 | 27330 |  ...   | ...
  </code></pre>
  
  <p>ID's should be split so that <code>max_id</code> for first group should be smaller than <code>min_id</code> from second group and so on.</p>
  
  <p>If any one has idea how to write such query (or plsql function if there is no other way), I would be grateful.</p>
  
  <p>I am using Postgresql 9.5.</p>