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
