Re: Range types

Tom Lane wrote:
> Dimitri Fontaine <dfontaine@hi-media.com> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> Hm, how would you do it with LATERAL?  The problem is not so much
>>> composition as the need for a variable number of rounds of
>>> composition.
> 
>> Let's have a try at it:
> 
>> select p2_member, array_accum(p1)
>>   from unnest(p2) as p2_member
>>        lateral (select period_except(p1_member, p2_member)
>>                   from unnest(p1) p1_member) as x(p1);
> 
> I don't think that does it.  Maybe I misunderstand LATERAL, but what
> that looks like to me is that each p1 will be separately filtered by
> each p2, giving rise to a distinct element in the output.  What we
> need is for each p1 to be filtered by *all* p2's, successively
> (though in any order).
> 
>             regards, tom lane

That approach will only work if you coalesce your inputs into 
non-contiguous sets (NCS) first. Overlapping ranges would break it in a 
hurry. In addition to two coalesce operations, period_except would be 
calculated 1000x for a pair of 100 element arrays. Original solution, 
while not short was probably a little more elegant than Tom gave credit 
for. In a single pass it pulls out only the data points needed to build 
the resultant NCS without making assumptions that the inputs were coalesced.

I think I'll still be able to do a single pass solution for continuous 
ranges. I just wont be able to do the coalesce operations inline with 
the set operations.

Scott