Skip site navigation (1)
Skip section navigation (2)
## Range Types and length function

### Responses

### pgsql-hackers by date

Currently, there is no way to define a generic "length" function over range types, which would give you the distance between the boundary points. It sounds simple, but the system actually needs quite a lot more information to accomplish that: * a function that subtracts two values of the range's subtype * it needs to know the result type of that function, which might not be the subtype (for instance, for timestamp the difference type would be interval) * it needs to know the "zero" value of the subtype for empty ranges * it also needs to know how to canonicalize discrete ranges for meaningful results -- what's the length of [10,10]? If you write a difference "canonical" function should the result be different? I suppose so. Even if the system knows all of that, we might run into problems with the type system, because if you have a generic function: f(anyrange) -> anyelement how would it know whether "anyelement" should be the subtype (e.g. if "f" is the function "upper") or the difference type (e.g. if "f" is the function "length")? My solution to all of this is somewhat simplistic, but the best idea I have so far: create function length(anyrange) returns anyelement language sql as $$ select case when $1? then upper($1) - lower($1) else '0' end; $$; And then, for timestamp[tz] and date, just define specific functions for those like: create function length(tsrange) returns interval language sql as $$ select case when $1? then upper($1) - lower($1) else '0 s' end; $$; In other words, special case the range types where the "difference type" is not the same as the subtype, and rely on function overloading to sort them out. These work for the most part, but they have a few problems: 1. It assumes that "-" really means "minus" and is defined effectively over the subtypes. 2. It assumes that '0' is valid input for the "zero" value of the subtype. 3. If the difference type is not the same as the subtype, and you forget to define the special-case function, then you are bound to get a cryptic error. I suppose the "right" way to solve these problems would be: 1. Force users to supply the "minus" function. 2. Force users to supply the "zero" value as a constant of the same type as the minus function's return value. 3. Check to see if the minus function's return type is different from the subtype. If so, automatically create a new entry in the catalog for the "length" function. I suppose it's not out of the question to do all of that work, but it seems like a little much just to get the generic length() function. I don't mind leaving it as-is, and I think it's a fairly reasonable solution. But I thought I would re-open it for discussion in case someone has a better idea. The length() function is obviously an important function to provide. Regards, Jeff Davis

- Re: Range Types and length function at 2011-06-26 12:45:20 from Greg Stark
- Re: Range Types and length function at 2011-06-26 22:37:03 from Florian Pflug

Next: From:Darren DuncanDate:2011-06-26 07:57:55Subject: Re: Range Types, constructors, and the type systemPrevious: From: Greg StarkDate: 2011-06-26 01:01:36Subject: Word-smithing doc changes