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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
