Hi,

Another sybase migration question. In Sybase, I can do something like this:

And this results in two tables being returned (for example, in Java, you'd have two ResultSet objects).

Is this sort of thing possible with PG? Note: I'm not asking if I can return multiple rows in a single table.

I don't think so, no.

Edit: I always wonder what the use case for such a thing is.
In 20 years I have never felt the need to return more than one result set from a function or procedure.

I agree. I think you need a different strategy. What are you trying to do?

We use JIRA and Confluence. If you know those products, the rest of this will make (hopefully) sense.

We use a confluence macro to execute arbitrary SQL queries against our JIRA instance. There is a 'sql' plugin that lets us do this. And it's the nature of the plugin that it can handle multiple resultsets in a way that is pleasingly laid out (it generates HTML we like).

In other words, the way it handled multiple single-row, multi-column tables is exactly what we wanted, HTML-formatting wise.

Now that I'm using postgres, the plugin handles a single multi-row, multi-column table quite differently.

So, because the ouput is being manhandled by the plugin, I need the output to match what the plugin is expecting.

I've kludged something up. There's a way to get the output of the plugin to be recursively evaluated. So now what I do is write one plugin call, which calls my 'outer' PG function. This first function generates one row each, for each 'table/resultset' I was generating in the sybase version. And in these rows I concatenate text to form a second plugin call, which is then evaluated by Confluence, and the second plugin call invokes an 'inner' PG function, which generates my single-row, multi-column table.

so yes, it's "n+1" calls to postgresql.

I don't know the plugin, but did you try to simply pass two SELECTs to the plugin separated by semicolon?
SELECT * FROM function_1(); SELECT * FROM function_2();

but my multiple calls, is as a result of a function itself. It's not two different functions, it's the same function with multiple parameter values, found through a separate function. And it's also not just 2, it's n.

For what it's worth, here's the conversation on the Confluence side (bottom of page) : https://studio.plugins.atlassian.com...nce+SQL+Plugin

oh, I think the light came on.

Instead of my outer function returning multiple rows, each one containing a single call to the plugin containing a SQL statement, I can make the outer function return a single call to the plugin, with multiple SQL statements.

I think that would then be 1+1 ? I guess it depends what the JDBC driver does.