Re: Understanding behavior of SELECT with multiple unnested columns

On 28/03/13 03:03, Tom Lane wrote:
> Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> writes:
>> The rule appears to be,
>> where N_x & N_y are the number of entries returned for x & y:
>> N_result = is the smallest positive integer that has N_x & N_y as factors.
> Right: if there are multiple set-returning functions in a SELECT list,
> the number of rows you get is the least common multiple of their
> periods. (See the logic in ExecTargetList that cycles the SRFs until
> they all report "done" at the same time.) I guess there's some value
> in this for the case where they all have the same period, but otherwise
> it's kind of bizarre. It's been like that since Berkeley days though,
> so I doubt we'll consider changing it now. Rather, it'll just be
> quietly deprecated in favor of putting SRFs into FROM (with LATERAL
> where needed).
>
> regards, tom lane
It surprised me when I first came across the behaviour, but I can't say
it is wrong!

I have used the current behaviour to create test data.

It might be useful to have a means of supplying the starting indices
list and the number of rows to generate (which would not only allow the
number to be less than the natural period, but also greater). I think
this would be very useful to create test data. Best if the facility
could take any source of values, including SELECT statements. Probably
not 'Politically Correct', but making it an SQL construct might be less
confusing than a function. Also, either, but not both of the indices
list, or the limit, could be omitted to get their default values.

Cheers,
Gavin