Re: Record with a field consisting of table rows

On 15 Jan 2011, at 17:01, Jon Smark wrote:

> Hi,
>
>> Nope, see my reply from yesterday around 20:23
>> You can return a table instead, with the count added as an
>> extra column.
>
> I did see your solution, but note that it does not return a tuple
> consisting of an integer and a setof (as I wanted), but instead
> returns a setof of a tuple.

No, of course not. A function cannot return different amounts of different return-values in any language I know of.

You don't seem to grasp the essence of what a set-returning function does; they are similar to Iterators in Java or functions that yield a result (as opposed to returning one) in Python or cursors in SQL.
You can't mix that with returning a single value, unless you do that each time the function is called for the next iteration (which happens to be what I did in my earlier example).

You could do something ugly by collecting the results in memory until you eventually return them in an array, but that would hardly be practical if your data sets get a little large.

You probably can return a tuple consisting of an int and a refcursor though. You'll need another function to loop through the refcursor to fetch the results, but you would sort of get what you apparently want. I can't see why you'd want that though.

You may also be able to return the count as an OUT-parameter, but I'm not sure you can mix that with returning a SETOF something else. I expect you'll find out that isn't possible. Obtaining both results from the function (if it _is_ possible) will be tricky I think.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d31da3e11871342110173!