Having a function return the minimum and maximum of an array is returning one value. The value is an array of size 2. Having a function return an array of the minimum, maximum and the number of states in a given country is returning two values.
You are asking PG to return multiple unrelated data sets. The number of use cases that this is useful and good is pretty much for displaying reports. Do two queries. The problem is already solved.
"You are asking PG to return multiple unrelated data sets
Who talked about 'unrelated'?
Just as one can return min, max, average and standard deviation of column C in table T in one call, one can, for example, return that, the top ten records with highest C value and the records with the top ten most common values for C.
Yes, you can do two queries, but doing them in one go can be faster, some times much faster.
The risk, of course, is that one gets "one stored procedure per screen", but as long as one is aware of that, I don't see anything inherently wrong with that.
I can think of a handful of scenarios where the second result set needs to be based on something from the first, and combining them can make some sense in that case, but it's a stretch. For "long running process" or "slow query time" arguments... eh... that may be a problem, but it'd be very much an edge case for most users of the DB engine in question.
Dunno if Oracle supports this or not, but pretty sure MySQL doesn't and PG doesn't. MSSQL is the only DB engine I know that supports multiple result sets from one stored procedure. If you want to tie yourself to features of just one DB, that's great - most people do in one way or another, just don't expect everyone else to support that particular feature or syntax.
Having a function return the minimum and maximum of an array is returning one value. The value is an array of size 2. Having a function return an array of the minimum, maximum and the number of states in a given country is returning two values.
You are asking PG to return multiple unrelated data sets. The number of use cases that this is useful and good is pretty much for displaying reports. Do two queries. The problem is already solved.