From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Scott Bailey <artacus(at)comcast(dot)net> |
Cc: | hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Range types |
Date: | 2009-12-15 23:22:24 |
Message-ID: | 16248.1260919344@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I wrote:
> The proposed problem is certainly soluble without any assumptions
> of discreteness.
To be concrete, I think it could be approached like this:
Assume the datatype provides a built-in function
period_except(p1 period, p2 period) returns setof period
which can return zero, one, or two rows depending on the inputs:
no rows if p1 is completely contained in p2
one row if p1 partially overlaps p2, for example:
[1,4] except [3,5] returns [1,3)
[4,6] except [1,5) returns [5,6]
two rows if p1 properly contains p2, for example
[1,10] except [4,5] returns [1,4) and (5,10]
[1,10] except [9,10) returns [1,9) and [10,10]
and of course just p1 if p1 and p2 don't overlap at all.
Given such a function it's a simple matter of successively removing each
element of p2[] from the set representing the current members of p1[].
The way that I'd find most natural to code that is a loop, along the
lines of
foreach p2_member in unnest(p2) loop
p1 := array(select period_except(p1_member, p2_member)
from unnest(p1) p1_member);
end loop;
But maybe it can be done in a single SQL command.
As this example makes clear, when dealing with continuous intervals you
*must* admit both open and closed intervals, else you don't have a way
to represent the results of "except". Maybe part of the failure to
communicate here arises from your desire to try to avoid supporting both
kinds of intervals. But I think you really have to do it if you want to
deal with data that hasn't got any natural granularity.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2009-12-15 23:40:47 | Re: Range types |
Previous Message | Scott Bailey | 2009-12-15 22:40:20 | Re: Range types |