Range Types and length function - Mailing list pgsql-hackers

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
resulttype 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
discreteranges 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