Sometimes I have seen this feature used to hide multiple round trips and intermediate result processing from the clients
Some others is simply because the client is generating a predefined data structure that has little resemblance to the schema and this "feature" becomes convenient
Some others is "ad-hoc" tools that allow to see multiple datasets in the UI, etc...
Anyway I just saw this in the TODO list: (http://wiki.postgresql.org/wiki/Todo) :
_ Implement stored procedures
This might involve the control of transaction state and the return of multiple result sets
Does anybody know if any the committers are working on that list?
On Saturday, November 30, 2013 5:21 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Alban Hertroys wrote on 30.11.2013 22:34:
>> - Multiple result sets > > Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?
Yes, basically something like this:
create procedure foobar() begin select * from table_1; select * from table_2 end;
I know that at least SQL Server and MySQL can do that. Maybe others as well (Sybase most probably).
But I always failed do see the actual advantage of that because the results can't be "used" any further (e.g. in a JOIN or a subselect). I also don't understand why having a single procedure doing a lot of stuff is better than having several procedures (or functions) doing one defined thing.
From a JDBC point of view the code simply asks the Statement whether it has any more result sets and loops over this until all results are returned.
