Hi Thomas,
Thanks for your fast and detailed answer.
The example in postgres (using plpgsql) would be:
CREATE OR REPLACE FUNCTION getVector(integer) RETURNS SETOF integer AS
$$
DECLARE
i integer;
BEGIN
FOR i IN 1..$1 LOOP
RETURN NEXT i-1;
END LOOP;
RETURN;
END
$$
LANGUAGE 'plpgsql';
------------ example
select * from getVector(3);
getvector
-----------
0
1
2
create table example (value integer);
insert into example (value) values (3);
insert into example (value) values (2);
select getVector(value) from example;
getvector
-----------
0
1
2
0
1
Even though with your answer it would be possible to get this behave
in H2:
> call getCombinedResult(
> 'select value from example',
> 'call getVector(?1)');
it is kind of difficult for a normal user to use it compare with the
previous postgres example, and I think it would be kind of tricky
to use it in a more complex SQL sentences.
are you planning to develop this behave in a future H2 release? or the
only alternative is to use it the way you told me with
'getCombinedResult'?
Thanx,
Jose Martinez-Llario
On Jun 6, 10:49 am, Thomas Mueller <[email protected]>
wrote:
> 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.
