I had proposed a temporal contrib module earlier and you wanted to see
support for many range types not just timestamptz. So I had an idea on
how to implement this but I want to see if you guys thought it was a
viable idea.
So basically I have an anyrange pseudo type with the functions prev,
next, last, etc defined. So instead of hard coding range types, we would
allow the user to define their own range types. Basically if we are able
to determine the previous and next values of the base types we'd be able
to define a range type. I'm envisioning in a manner much like defining
an enum type.
CREATE TYPE periodtz AS RANGE (timestamptz, '1 microsecond'::interval);
CREATE TYPE numrange AS RANGE (numeric(8,2));
-- determine granularity from typmod
CREATE TYPE floatrange AS RANGE (float, '0.000000001'::float);
Or getting really crazy...
CREATE TYPE terms AS ENUM ('2000_F', '2000_W', '2000_S', '2000_Su'... '2010_F', '2010_W', '2010_S', '2010_Su');
CREATE TYPE termrange AS RANGE (terms);
So basically I have a pg_range table to store the base typeid, a text
field for the granule value and the granule typeid.
I doubt we would be able to get this in for the 8.5 release, especially
since I'm still learning C and the Postgres internals. Jeff Davis is
going to get something in before the next commit fest so we'll have some
type of temporal/range support. But we wanted to see what direction the
community felt we should go.
Scott Bailey
