2011/1/5 Jeff Davis <pg...@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_zmcuhpoet3gaqq6p...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
