I'd like to write a PLPGSQL (targeting PostgreSQL 9.3) function which will return multiple result sets (i.e. when accessed through JDBC I'll call getMoreResults() to move to the next set of rows), but everything I've tried either gives me a syntax error or simply concatenates everything together into a single result set.
Here is a simple example that illustrates the issue:
CREATE TYPE my_type AS (a BIGINT, b TEXT, c DOUBLE PRECISION);
CREATE FUNCTION my_func(_arg1 TEXT, _arg2 TEXT)
RETURNS SETOF my_type
AS $$
BEGIN
RETURN QUERY SELECT a, b, c FROM table1 WHERE d = _arg1 AND e = _arg2;
RETURN QUERY SELECT a, b, c FROM table2 WHERE d = _arg1 AND e = _arg2;
END;
$$ LANGUAGE PLPGSQL;
When I run this the rows from both table1 and table2 get concatenated together into a single result set.
I've also tried something like
CREATE FUNCTION my_func(_arg1 TEXT, _arg2 TEXT)
RETURNS SETOF TABLE(a BIGINT, b TEXT, c DOUBLE PRECISION)
But this just results in a cryptic syntax error: ERROR: syntax error at end of input.
For completeness sake here is the Java code I'd like to use to process the results. I'm fairly certain the issue is on the db function side, but it's possible I'm misunderstanding how the JDBC API is supposed to work.
(Error handling and resource closing removed for readability)
PreparedStatement statement = connection.prepareStatement("SELECT * FROM my_func(?, ?);");
statement.setString(1, "foo");
statement.setString(2, "bar");
statement.execute();
ResultSet rs1 = statement.getResultSet();
while(rs1.next()) {
// Process first result set
}
statement.getMoreResults();
ResultSet rs2 = statement.getResultSet();
while(rs2.next()) {
// Process second result set
}
Based on the searching I've done so far it seems like this type of solution is supposed to be supported by JDBC and PostgreSQL, but I can't find any explicit examples of it in action.
