Re: Range types - Mailing list pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, Dec 13, 2009 at 11:49:53PM -0800, Scott Bailey wrote:
> I had proposed a temporal contrib module earlier and you wanted to see 
> support for many range types not just timestamptz [...]

> 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);

I might be asking the same as Itagaki is (see below) but... are you just
envisioning ranges on 'discrete' types? What about ranges on floats or
(arbitrary length) strings, where there is no prev/next? Too difficult?
(mind you: I don't know exactly what I'm talking about, but in would be
definitely useful).

On Mon, Dec 14, 2009 at 05:10:24PM +0900, Takahiro Itagaki wrote:
> 
> Scott Bailey <artacus@comcast.net> wrote:
> 
> > CREATE TYPE periodtz AS RANGE (timestamptz, '1 microsecond'::interval);
> 
> What does the second argument mean? Is it a default interval?

I think it's the granularity. That defines how to find the 'next' point
in time. As I understood it, Scott envisions ranges only for discrete
types, i.e. those advancing in well-defined steps. Note that (a) I might
be wrong and (b) there might be a very good reason for doing it this way.

> > So basically I have a pg_range table to store the base typeid, a text 
> > field for the granule value and the granule typeid.
> 
> As another approach, what about storing typeid in typmod?
> (Oid can be assumed to be stored in int32.)
> 
> For example,
>     CREATE TABLE tbl ( r range(timestamp) );
>     SELECT '[ 2.0, 3.0 )'::range(float);
> 
> There might be some overhead to store typeid for each range instance,
> but the typmod approach does not require additinal catalogs and syntax
> changes. It can be possible even on 8.4.

This looks more natural to me too.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLJgAZBcgs9XrR2kYRAljHAJwJjYV6fHz4qPSY6sXROYZ6pKIlGQCeO4X1
eszUJopVGqcPkXbiHdQOVrs=
=IYQ0
-----END PGP SIGNATURE-----