Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Question Unanswered: Returning multiple sets (sets of sets?) from a function

    Hi,

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

    Code:
     CREATE PROCEDURE fooFunction AS
    
    SELECT * FROM table1
    
    SELECT * FROM table2
    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by MikeCWpg View Post
    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?
    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.
    Last edited by shammat; 08-27-10 at 13:52.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I agree. I think you need a different strategy. What are you trying to do?

  4. #4
    Join Date
    Aug 2010
    Posts
    6
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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();

  6. #6
    Join Date
    Aug 2010
    Posts
    6
    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
    Last edited by MikeCWpg; 08-27-10 at 18:08.

  7. #7
    Join Date
    Aug 2010
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •