Re: WIP: Range Types - Mailing list pgsql-hackers

On Thu, Jan 06, 2011 at 02:25:01AM +0900, Hitoshi Harada wrote:
> 2011/1/5 Jeff Davis <pgsql@j-davis.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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