Hi, > 2) The sentence "select getVector(3);" returns: > ((0), (1), (2))
Yes, it returns one row with one value. The value contains the result set. > 3) The sentence "select X from getVector(3);" works in the way that I want. In this case the function called as it would be a table. > "select getVector(value) from example;" > "select * from (select getVector(value) from example) as foo;" This both interprets the getVector result as a value (that contains the result set). > The result I was expecting to get (like in PostgreSQL) would be a > table made up with 6 rows: > 0 > 1 > 2 > 0 > 1 I'm afraid this is not supported in H2, at least not in this way. Could you post a PostgreSQL example if you have one? But I'm sure the problem can be solved in H2. Example: http://h2database.com/p.html#ba5c002e6f13a9e55ae33ed3c0725550 drop all objects; create table example (value integer); insert into example (value) values (3); insert into example (value) values (2); create alias getVector as $$ import java.sql.*; import org.h2.tools.SimpleResultSet; @CODE ResultSet getVector(Connection conn, Integer size) throws SQLException { SimpleResultSet rs = new SimpleResultSet(); rs.addColumn("X", Types.INTEGER, 10, 0); String url = conn.getMetaData().getURL(); if (url.equals("jdbc:columnlist:connection")) { return rs; } for (int s = size.intValue(), x = 0; x < s; x++) { rs.addRow(x); } return rs; } $$; create alias getCombinedResult as $$ import java.sql.*; import org.h2.tools.SimpleResultSet; @CODE ResultSet getCombinedResult(Connection conn, String outer, String each) throws SQLException { SimpleResultSet result = null; Statement stat = conn.createStatement(); ResultSet rs = stat.executeQuery(outer); PreparedStatement prep = conn.prepareStatement(each); String url = conn.getMetaData().getURL(); while (rs.next()) { int oc = rs.getMetaData().getColumnCount(); for (int i = 0; i < oc; i++) { prep.setString(i + 1, rs.getString(i + 1)); } ResultSet rs2 = prep.executeQuery(); while(rs2.next()) { ResultSetMetaData meta = rs.getMetaData(); int columnCount = meta.getColumnCount(); if (result == null) { result = new SimpleResultSet(); for (int i = 0; i < columnCount; i++) { result.addColumn( meta.getColumnLabel(i + 1), meta.getColumnType(i + 1), meta.getPrecision(i + 1), meta.getScale(i + 1) ); } if (url.equals("jdbc:columnlist:connection")) { return result; } } Object[] row = new Object[columnCount]; for (int i = 0; i < columnCount; i++) { row[i] = rs2.getObject(i + 1); } result.addRow(row); } } return result; } $$; call getCombinedResult( 'select value from example', 'call getVector(?1)'); Regards, Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
