Re: WIP: Range Types

On Thu, Jan 06, 2011 at 02:25:01AM +0900, Hitoshi Harada wrote:
> 2011/1/5 Jeff Davis <pgsql(at)j-davis(dot)com>:
> > On Tue, 2011-01-04 at 23:04 +0900, Hitoshi Harada wrote:
> >> >  CREATE TYPE numrange
> >> >    AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp);
> >>
> >> I am interested in how you define increment/decrement operation
> >> of range value in discrete types. The window functions and
> >> PARTITION also want to represent RANGE but there's no clear
> >> solution.
> >
> > The user would specify a "canonical" function like:
> >
> >   CREATE TYPE int4range AS RANGE (SUBTYPE=int4, SUBTYPE_CMP=btint4cmp,
> >     CANONICAL=my_int4range_canonical);
> >
> > That function would be called when constructing ranges on input or after
> > a computation, and could change something like (1,4] into [2,4] if you
> > prefer the latter form.
> >
> > So the range types would not have increments, decrements, granules, or
> > knowledge about the "difference" type (e.g. "interval" is the difference
> > type for timestamp).
>
> To canonicalize, it might be ok. I wonder if you won't operate on
> the range types like extending their bounds or iterate/enum values
> from start to end. In such situation, I bet you'll need to know how
> to walk values step by step.
>
> > What support do you need/want from range types to help with new window
> > function features?
> >
> My argument is here:
> http://archives.postgresql.org/message-id/AANLkTimFmQmbzJ5CTXvE_PwT_zmCuHPoet3gaQq6Pvo8@mail.gmail.com
>
> 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?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate