Given the original:
CREATE TABLE Example (start REAL, stop REAL, tag TEXT);
I suppose it might make more sense to do it like this:
CREATE FUNCTION remove_bad(gs REAL, gf REAL, bs REAL, bf REAL)
RETURNS SETOF Example AS $$
BEGIN
CASE
WHEN ((bs <= gs) AND (bf < gf)) THEN
RETURN QUERY VALUES (bf, gf, 'interesting');
WHEN ((bs > gs) AND (bf < gf)) THEN
RETURN QUERY VALUES (gs, bs, 'interesting'),
(bf, gf, 'interesting');
WHEN ((bs > gs) AND (bf >= gf)) THEN
RETURN QUERY VALUES (gs, bs, 'interesting');
WHEN ((bs <= gs) AND (bf >= gf)) THEN
RETURN;
END CASE;
END; $$
LANGUAGE plpgsql;
SELECT remove_bad(10,40, 05,15);
SELECT remove_bad(10,40, 10,15);
SELECT remove_bad(10,40, 20,30);
SELECT remove_bad(10,40, 20,40);
SELECT remove_bad(10,40, 20,45);
SELECT remove_bad(10,40, 05,40);
SELECT remove_bad(10,40, 05,45);
SELECT remove_bad(10,40, 10,40);
SELECT remove_bad(10,40, 10,45);
