agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Adam Jensen <hanzer@riseup.net>
To: pgsql-sql@lists.postgresql.org
Subject: Re: interval origami
Date: Fri, 30 Nov 2018 19:45:35 -0500
Message-ID: <ecab3748-7ba6-278f-10ad-e31bdee865c4@riseup.net> (raw)
In-Reply-To: <a824b8c9-24d3-d577-c2d8-7845a1da6646@riseup.net>
References: <20181130175719.l7izv65wjwgkls35@alvherre.pgsql>
	<d05565aa-551d-fac3-ba88-8215c8cbe0d4@riseup.net>
	<6d32b24f-2d3e-df79-81d2-3b1abb8cc4b4@riseup.net>
	<80ea3708-11be-e7e3-9f49-011a682e3bf4@riseup.net>
	<a824b8c9-24d3-d577-c2d8-7845a1da6646@riseup.net>

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

view thread (10+ messages)  latest in thread

Message-ID: <ecab3748-7ba6-278f-10ad-e31bdee865c4@riseup.net>
Permalink:  ../ecab3748-7ba6-278f-10ad-e31bdee865c4@riseup.net/
Also on:    postgresql.org/message-id/ecab3748-7ba6-278f-10ad-e31bdee865c4@riseup.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: hanzer@riseup.net, pgsql-sql@lists.postgresql.org
  Subject: Re: interval origami
  In-Reply-To: <ecab3748-7ba6-278f-10ad-e31bdee865c4@riseup.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox