Re: [HACKERS] [PROPOSAL] Temporal query processing with range types

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