title: H2 Database: Get rows from subquery as array
body: <p>I have a query which works fine with PostgreSQL, and I need to use it on H2Database.</p>
  
  <p>Important for the tables is basically only an <code>id integer</code> field.</p>
  
  <p>The example-query on PostgreSQL with the result is here:</p>
  
  <pre><code>select id, 
  array_to_string(
      array(select id from table1)
  ,',') 
  from table2
  order by id
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>id | array_to_string
  2  | 1,3,4,5,2
  3  | 1,3,4,5,2
  4  | 1,3,4,5,2
  6  | 1,3,4,5,2
  7  | 1,3,4,5,2
  8  | 1,3,4,5,2
  9  | 1,3,4,5,2
  10 | 1,3,4,5,2
  </code></pre>
  
  <p>For H2, I implemented the user-defined functions <code>array_to_string</code> and <code>array</code> as follows:</p>
  
  <pre><code>public class H2Functions {
  
      public static String arrayToString(final Object[] array, final String separator) {
          return StringUtils.join(array, separator);
      }
  
      public static Object array(final Object row) {
          return "???";
      }
  
  }
  </code></pre>
  
  <p>The problem is that I can not implement <code>array</code> as I don't know what it get's passed.</p>
  
  <p>The query fails with:</p>
  
  <pre><code>Scalar subquery contains more than one row;
  </code></pre>
  
  <p>How can I convince H2 to return something which <code>array</code> can work with ?</p>