On Wed, 2011-01-05 at 10:41 -0800, David Fetter wrote:
> On Thu, Jan 06, 2011 at 02:25:01AM +0900, Hitoshi Harada wrote:
> > For any type to calculate boundary based on RANGE clause in window
> > functions, we need some type interface mechanism in the core to know
> > how to add / subtract values to reach the boundary from the current
> > value. For example,
> >
> > SELECT count(*) OVER (ORDER BY n_int RANGE BETWEEN 10 PRECEDING AND 5
> > FOLLOWING) FROM tbl;
>
> I'm not sure I get the connection between this type of range and the
> "range types" Jeff is working on. Jeff's work involves a way to
> create types which represent ranges over types which have some kind of
> ordering, although not necessarily a successor operation.
>
> Had you planned to cast to an integer range in the process of doing
> this window?
I don't think Harada-san needs the type infrastructure itself, just the
interface to find the "difference type" (e.g. "interval" from
"timestamp") as well as functions like add and subtract (potentially two
interesting subtract functions). Without knowing which function to call,
there is no way to find the window boundaries given the current row.
The current design for range types doesn't ask for add or subtract.
Although it might be interesting to try to use such an interface for
range types, it introduces a lot of complexity and makes it easier to
cause subtle problems (consider that addition of timestamps and
intervals is not commutative).
Even if add and subtract were associated with a range type, there's no
way to tell which range type to pick given the window function syntax
(multiple range types could be defined over the same subtype).
I think the interface question should be addressed more directly with a
"type interfaces" patch.
Regards,Jeff Davis