Re: Some feedback on range types - Mailing list pgsql-general

On Wed, 2012-07-18 at 14:33 -0700, Scott Bailey wrote:
> I'm testing range types and I've come up with a couple of curiosities.
>
> 1) I'll start off easy. In the wild, discrete ranges tend to be
> closed-closed [] while continuous ranges tend to be closed-open [). For
> instance, on Tuesday stock traded at [28.34, 32.18] or Bob was employed
> [2009-06-01, 2012-04-15] or Sally lived [1934, 2001]. But these ranges
> are all converted to [). So Sally's tombstone ends up reading
> [1934-2002). Not a huge deal, but it is difficult for users to change
> this behavior.

I don't really have a good answer for this. We could supply alternate
output functions that allow you to specify how a discrete range is
displayed.

> 2) Typemod doesn't work for subtypes. So say I'm working on a
> stock-trading app and I want to create a numeric range with a base type
> of numeric(8,2) and a granularity of 0.01.
>
>     CREATE TYPE num_range AS RANGE (SUBTYPE = numeric(8,2));
>     SELECT num_range(0.2, 2/3.0);
>     -->  [0.2,0.66666666666666666667)

Will fix. I haven't gotten around to it yet; it's actually quite a bit
of code (unless I'm missing something).

> 3) Continuing with the above example, I make a canonical function then
> hack it in to the system catalog to temporarily get around the
> chicken/egg problem mentioned earlier.
>
>     CREATE OR REPLACE FUNCTION num_range_canonical(num_range)
>     RETURNS num_range AS
>     $$
>         SELECT num_range(
>             (CASE WHEN lower_inc($1) THEN lower($1)
>             ELSE lower($1) + 0.01 END)::numeric(8,2),
>             (CASE WHEN upper_inc($1) THEN upper($1)
>             ELSE upper($1) - 0.01 END)::numeric(8,2),
>             '[]');
>     $$ LANGUAGE 'sql' IMMUTABLE STRICT;
> However, the built in range types are automatically canonicalized while
> a user created one is not, even with the canonical function set on the
> type. Not a huge problem, but not an expected behavior either.

I assume that this isn't a problem when defining it in C using the
method mentioned in the other thread.

> 4) No editing in place. This is a problem when trying to create
> functions that will work with anyrange. Some missing functionality was
> the ability to do set difference when the first range extends on both
> sides of the second. The function range_minus throws an exception in
> that situation. So I set about to add the functions range_ldiff and
> range_rdiff to pull out the left or right piece in this situation.
> Because users can add any number of range types it would be very to
> create a new instance of the correct type. It would be much easier to
> just edit the upper or lower bounds of one of the input parameters. But
> that doesn't seem to be supported.

If I understand the problem correctly, it's a little more clear to solve
it with the C API. Specifically, the range_get_typcache() and
make_range() functions. You can see a similar pattern use in many of the
generic range functions defined in rangetypes.c, like range_union().

I agree it would be nice to make it easier to define new range type
functions with other PLs and not be so reliant on C.

I like the idea of having functions that return a range of the same type
but with some modification. Not quite update-in-place as you suggest,
but accomplishes the same thing.

Regards,
    Jeff Davis