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

On 30 November 2017 at 17:26, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I wonder if we could instead think about R NORMALIZE S ON R.x = S.y
> WITH (R.time, S.time) as an ordinary join on R.x = S.y with some extra
> processing afterwards.

That would work nicely, kindof like a Projection, but one that can
vary the number of rows emitted.

For normal joins, we simply emit one row. For new style joins we call
a special PostJoinSetProjection function: one tuple in, potentially
many tuples out.

Peter, does Robert's proposed treatment give you what you need?

Overall, I like the goals expressed on this thread. I think if we
should focus on introducing useful new functionality, rather than
focusing on syntax.

I'm not very keen on adopting new syntax that isn't in the
SQLStandard. They have a bad habit of doing something completely
different. So a flexible approach will allow us to have functionality
now and we can adopt any new syntax later.

For any new syntax, I think the right approach would be to create a
new parser plugin. That way we could make all of this part of an
extension.
* a parser plugin for any new syntax
* various PostJoinSetProjection() functions to be called as needed
So the idea is we enable Postgres to allow major new functionality, as
was done for PostGIS so successfully.

We can adopt syntax into the main parser later once SQLStandard
accepts this, or some munged version of it.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services