Re: interval origami - Mailing list pgsql-sql
From | Adam Jensen |
---|---|
Subject | Re: interval origami |
Date | |
Msg-id | 80ea3708-11be-e7e3-9f49-011a682e3bf4@riseup.net Whole thread Raw |
In response to | Re: interval origami (Adam Jensen <hanzer@riseup.net>) |
Responses |
Re: interval origami
|
List | pgsql-sql |
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?