From: | Adam Jensen <hanzer(at)riseup(dot)net> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: interval origami |
Date: | 2018-12-01 00:45:35 |
Message-ID: | ecab3748-7ba6-278f-10ad-e31bdee865c4@riseup.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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);
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2018-12-01 13:24:25 | Re: interval origami |
Previous Message | Adam Jensen | 2018-12-01 00:28:38 | Re: interval origami |