How to create a new date range type with included upper bound in Postgres?

Question

Postgres comes with a nice feature called Range Types that provides useful range functionality (overlaps, contains, etc).

I am looking to use the daterange type, however I think the type was implemented with an awkward choice: the upper bound of the daterange is excluded. That means that if I defined my value as 2014/01/01 - 2014/01/31, this is displayed as [2014/01/01, 2014/01/31) and the 31st of January is excluded from the range!

I think this was the wrong default choice here. I cannot think of any application or reference in real life that assumes that the end date of a date range is excluded. At least not to my experience.

I want to implement a range type for dates with both lower and upper bounds included, but I am hitting the Postgres documentation wall: References on how to create a new discrete range type are cryptic and lack any examples (taken from the documentation: Creating a canonical function is a bit tricky, since it must be defined before the range type can be declared).

Can someone provide some help on this? Or even directly the implementation itself; it should be 5-10 lines of code, but putting these 5-10 lines together is a serious research effort.

EDIT: Clarification: I am looking for information on how to create the proper type so that inserting [2014/01/01, 2014/01/31] results in a upper(daterange) = '2014/01/31'. With the existing daterange type this value is "converted" to a [2014/01/01, 2014/02/01) and gives a upper(daterange) = '2014/02/01'

Answer 1

Following the instructions on Postgres documentation I came up with the following code to create the type I need. However it won't work (read on).

CREATE TYPE daterange_;

CREATE FUNCTION date_minus(date1 date, date2 date) RETURNS float AS $$
    SELECT cast(date1 - date2 as float);
$$ LANGUAGE sql immutable;

CREATE FUNCTION dr_canonical(dr daterange_) RETURNS daterange_ AS $$
BEGIN
    IF NOT lower_inc(dr) THEN
        dr := daterange_(lower(dr) + 1, upper(dr), '[]');
    END IF;
    IF NOT upper_inc(dr) THEN
        dr := daterange_(lower(dr), upper(dr) - 1, '[]');
    END IF;
    RETURN dr;
END;
$$ LANGUAGE plpgsql;

CREATE TYPE daterange_ AS RANGE (
    SUBTYPE = date,
    SUBTYPE_DIFF = date_minus,
    CANONICAL = dr_canonical    
);

As far as I can tell, this definition follows the specification exactly. However it fails at declaring the dr_canonical function with ERROR: SQL function cannot accept shell type daterange_.

It looks like (code also) it is impossible to declare a canonical function using any language other than C! So it is practically impossible to declare a new discrete range type, especially if you use a Postgres cloud service that gives no access to the machine running it. Well played Postgres.