Tom Lane wrote:
Dimitri Fontaine <dfonta...@hi-media.com> writes:
Tom Lane <t...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
