Re: WIP: Range Types

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;

In the standard, the types allowed in RANGE are only int, float, date,
timestamp, etc. but we have various extensible data types as you know
and we couldn't assume '+' / '-' operators tell add /subtract
operation absolutely.

> Also, partitioning might have some use for range types to represent
> range partitions. Comments are welcome.

I heard about partitioning which may have the same problem in RANGE
clause from Itagaki-san, but have not looked so much yet.

Regards,

--
Hitoshi Harada