Re: [HACKERS] [PROPOSAL] Temporal query processing with range types - Mailing list pgsql-hackers

From Peter Moser
Subject Re: [HACKERS] [PROPOSAL] Temporal query processing with range types
Date
Msg-id CAHO0eLakQyY9dBxQ_2jWaLC6KxUTsURtfjq42Xf87+_-VVeLtw@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] [PROPOSAL] Temporal query processing with range types  (Simon Riggs <simon@2ndquadrant.com>)
List pgsql-hackers
On Sun, 2018-01-07 at 09:58 +0000, Simon Riggs wrote:
> > The attached README explains the ALIGN operation step-by-step with
> > a TEMPORAL LEFT OUTER JOIN example. That is, we start from a query
> > input, show how we rewrite it during parser stage, and show how the
> > final execution generates result tuples.
> Sorry, this was too complex for me.
>
> Can we get a much simpler example please?

Please see the following simpler example:

DROP TABLE budg;
CREATE TABLE budg(name VARCHAR(5), amnt INTEGER, t DATERANGE);
INSERT INTO budg VALUES ('Joe', 5, '[2012/2/1,2012/9/1)');
INSERT INTO budg VALUES ('Ann', 7, '[2012/5/1,2012/9/1)');
INSERT INTO budg VALUES ('Per', 3, '[2012/4/1,2012/10/1)');
SELECT * FROM budg AS r;

SELECT *
FROM ( budg r ALIGN budg s ON s.amnt > r.amnt WITH (t,t)) r
WHERE NOT EXISTS (
  SELECT *
  FROM (budg s ALIGN budg r ON s.amnt > r.amnt WITH (t,t)) s
  WHERE s.amnt > r.amnt
  AND r.t = s.t  );

--  name | amnt |            t
-- ------+------+-------------------------
--  Joe  |    5 | [2012-02-01,2012-05-01)
--  Ann  |    7 | [2012-05-01,2012-09-01)
--  Per  |    3 | [2012-09-01,2012-10-01)



Best regards,
Anton, Johann, Michael, Peter


pgsql-hackers by date:

Previous
From: Peter Moser
Date:
Subject: Re: [HACKERS] [PROPOSAL] Temporal query processing with range types
Next
From: Peter Moser
Date:
Subject: Re: [HACKERS] [PROPOSAL] Temporal query processing with range types