Re: interval origami - Mailing list pgsql-sql

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);