Re: interval origami

On 11/30/18 7:04 PM, Adam Jensen wrote:
> On 11/30/18 4:02 PM, Adam Jensen wrote:
>> On 11/30/18 3:19 PM, Adam Jensen wrote:
>>> The 'numrange' type with the 'overlaps' and 'intersection' operators
>>> seem to cover the fundamental computations in a very natural way.
>>
>> Actually, those operators might not be entirely sufficient. Given two
>> ranges like this:
>>
>> 10.0|39.0|interesting
>> 15.0|21.0|fail
>>
>> Something like the negative or inverse of the intersection is needed:
>>
>> 10.0|15.0|interesting
>> 21.0|39.0|interesting
>
> I've mapped out nine time segment overlap scenarios:
>
> 1. good(10, 40) | bad(05, 15) -> good(15, 40)
> 2. good(10, 40) | bad(10, 15) -> good(15, 40)
> 3. good(10, 40) | bad(20, 30) -> good(10, 20), good(30, 40)
> 4. good(10, 40) | bad(20, 40) -> good(10, 20)
> 5. good(10, 40) | bad(20, 45) -> good(10, 20)
> 6. good(10, 40) | bad(05, 40) -> good()
> 7. good(10, 40) | bad(05, 45) -> good()
> 8. good(10, 40) | bad(10, 40) -> good()
> 9. good(10, 40) | bad(10, 45) -> good()
>
> Letting gs/gf and bs/bf represent "good start-time"/"good finish-time"
> and so on, pseudo-code to remove the bad segments looks like this:
>
> find overlap: good(gs, gf) | bad(bs, bf)
>
> CASE
> WHEN ((bs <= gs) AND (bf < gf)) THEN # 1 & 2
> -> (bf, gf)
> WHEN ((bs > gs) AND (bf < gf)) THEN # 3
> -> (gs, bs), (bf, gf)
> WHEN ((bs > gs) AND (bf >= gf)) THEN # 4 &
> -> (gs, bs)
> WHEN ((bs <= gs) AND (bf >= gf)) THEN # 6 & 7 & 8 & 9
> -> ()
> END CASE;
>
> And my first attempt at writing a PostgreSQL function looks like this:
>
> CREATE FUNCTION find_overlap(gs REAL, gf REAL, bs REAL, bf REAL)
> RETURNS TABLE (start REAL, stop REAL) AS $$
> BEGIN
> CASE
> WHEN ((bs <= gs) AND (bf < gf)) THEN
> RETURN NEXT (bf, gf);
> RETURN;
> WHEN ((bs > gs) AND (bf < gf)) THEN
> RETURN NEXT (gs, bs);
> RETURN NEXT (bf, gf);
> RETURN;
> WHEN ((bs > gs) AND (bf >= gf)) THEN
> RETURN NEXT (gs, bs);
> RETURN;
> WHEN ((bs <= gs) AND (bf >= gf)) THEN
> RETURN;
> END CASE;
> END; $$
> LANGUAGE plpgsql;
>
> It results in:
>
> ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters
> LINE 6: RETURN NEXT (bf, gf);
>
> Page 83 of the book "PostgreSQL Server Programming" mentions this
> situation but doesn't actually describe or explain anything; nor does it
> present a working example...
>
> Any ideas?
>

This seems to work. Trial and error got me there.

CREATE FUNCTION find_overlap(gs REAL, gf REAL, bs REAL, bf REAL)
RETURNS TABLE (start REAL, stop REAL) AS $$
BEGIN
CASE
WHEN ((bs <= gs) AND (bf < gf)) THEN
RETURN QUERY VALUES (bf, gf);
WHEN ((bs > gs) AND (bf < gf)) THEN
RETURN QUERY VALUES (gs, bs), (bf, gf);
WHEN ((bs > gs) AND (bf >= gf)) THEN
RETURN QUERY VALUES (gs, bs);
WHEN ((bs <= gs) AND (bf >= gf)) THEN
RETURN;
END CASE;
END; $$
LANGUAGE plpgsql;

1. good(10, 40) | bad(05, 15) -> good(15, 40)
2. good(10, 40) | bad(10, 15) -> good(15, 40)
3. good(10, 40) | bad(20, 30) -> good(10, 20), good(30, 40)
4. good(10, 40) | bad(20, 40) -> good(10, 20)
5. good(10, 40) | bad(20, 45) -> good(10, 20)
6. good(10, 40) | bad(05, 40) -> good()
7. good(10, 40) | bad(05, 45) -> good()
8. good(10, 40) | bad(10, 40) -> good()
9. good(10, 40) | bad(10, 45) -> good()

SELECT find_overlap(10,40, 05,15);
SELECT find_overlap(10,40, 10,15);
SELECT find_overlap(10,40, 20,30);
SELECT find_overlap(10,40, 20,40);
SELECT find_overlap(10,40, 20,45);
SELECT find_overlap(10,40, 05,40);
SELECT find_overlap(10,40, 05,45);
SELECT find_overlap(10,40, 10,40);
SELECT find_overlap(10,40, 10,45);