Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Takahiro Itagaki on 14 Dec 2009 03:10 Scott Bailey <artacus(a)comcast.net> wrote: > CREATE TYPE periodtz AS RANGE (timestamptz, '1 microsecond'::interval); What does the second argument mean? Is it a default interval? > 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. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: tomas on 14 Dec 2009 04:06 -----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(a)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----- -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 14 Dec 2009 06:02 On Mon, Dec 14, 2009 at 4:06 AM, <tomas(a)tuxteam.de> wrote: >> 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. It 's very different than the way we've traditionally used typmod, though, which Tom described pretty well here: http://archives.postgresql.org/pgsql-hackers/2009-11/msg01183.php For example, function signatures ignore typmod, so you'll be able to write a function that takes a range, but you won't know what kind of range you're getting. Pavel proposed changing that, but the problem is that while you might want to discriminate on the basis of what sort of range you're getting, you probably DON'T want to discriminate on the length of the character string being passed in with a varchar argument, or the number of decimal places in a numeric. So I think this is going to be awkward. Also, typid is unsigned and typmod is signed. Again, awkward. Maybe with a big enough crowbar you can make it work, but it seems like it won't be pretty... ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: tomas on 14 Dec 2009 06:45 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Mon, Dec 14, 2009 at 06:02:04AM -0500, Robert Haas wrote: > On Mon, Dec 14, 2009 at 4:06 AM, <tomas(a)tuxteam.de> wrote: [...] > > This looks more natural to me too. > > It 's very different than the way we've traditionally used typmod, > though, which Tom described pretty well here: > > http://archives.postgresql.org/pgsql-hackers/2009-11/msg01183.php > > For example, function signatures ignore typmod, so you'll be able to > write a function that takes a range, but you won't know what kind of > range you're getting [...] > Also, typid is unsigned and typmod is signed. Again, awkward [...] Ugh. I see. Thank you for this very insightful comment. Regards - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLJiVuBcgs9XrR2kYRAp4ZAJsHjzYuVxwaeAUr1ogqRsZOecxdcwCeLfUv 8lZmeY6lb4r+57c6ZdB0J9M= =0Ips -----END PGP SIGNATURE----- -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Dimitri Fontaine on 14 Dec 2009 08:01
Scott Bailey <artacus(a)comcast.net> writes: > 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. It's not clear how to define those functions for the prefix_range datatype, where '123' represents any text begining with those chars and '123[4-6]' any text begining with '123' then either 4, 5 or 6. What's supposed to return SELECT next('123'::prefix_range); ? Regards, -- dim PS: as I'm used to use that in the telephony context, the example contain figures, but it's a text based type and given questions and reports in pgsql-general, people do use it with text ranges too. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |