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