Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Jeff Davis on 16 Dec 2009 17:25 On Wed, 2009-12-16 at 13:59 -0500, Tom Lane wrote: > For example, if you're trying to do classroom scheduling, it might be > useful to constrain the periods to start and end on hour boundaries > --- but the next thing you'll want is to have it know that the "next" > slot after 5pm Friday is 8am Monday. Except on holidays. And then > there's the fact that my alma mater starts most hour-long classes on > the half hour. Data types are only a first-level constraint -- a domain of reasonable values. The class isn't going to start on a fraction-of-a-minute boundary, so it would be reasonable to reject those values early. I never suggested that next() should be such a highly business-dependent function as you suggest above (skipping holidays, etc); it should just return the next value in the domain (if it's discrete). Surely you wouldn't suggest that the ipv4 data type's next() function should skip over addresses that aren't in a valid subnet on your network. But you seem to think those make useful discrete ranges. Regards, Jeff Davis -- 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: Jeff Davis on 16 Dec 2009 17:31 On Wed, 2009-12-16 at 14:29 +0100, tomas(a)tuxteam.de wrote: > This alone would practically preclude discrete -- int and float would > behave quite differently (float's "granules" growing towards the edges > or having to choose a bigger granule for float than for int in the first > place). It may be an argument for a different range type name, or trying to spot obviously dangerous things and throw an error. But I don't think it's an argument to prevent a superuser from defining a discrete range of whatever he or she wants, as long as they provide the necessary functions. Regards, Jeff Davis -- 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 16 Dec 2009 23:50 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wed, Dec 16, 2009 at 04:45:54PM -0300, Alvaro Herrera wrote: > tomas(a)tuxteam.de wrote: > > > (and as Andrew Dunstan pointed out off-list: I was wrong with my bold > > assertion that one can squeeze infinitely many (arbitrary length) > > strings between two given. This is not always the case). > > Of course you can do that if you assume lexicographical order, or any > other arbitrary order. Hm. On strings (lexicographical order) there's always a "next" (using the convention 'a' is the first symbol, 'z' the last and . the append operator): next(X) == X . 'a' (there is no string between X and X . 'a', and X < X . 'a') But there generally no prev! (unless the last "character" of a string is precisely 'a') prev("bob") == "bazzzzz......" ....and we just left the realm of finte-length strings. Strange structure this space, has. > some ordering on which this does not happen. And in fact there is: > order strings by length first, and then lexicographically. If you do > this then you have next() and prev() for any given string. If you use > ASCII only, you have a.next = b, and so on. Yes, this is similar to the trick of ordering the rationals in a "snake diagonal" like in [1], to prove their countability. But this sorting isn't very useful (except in this clever proof). > There is the argument that some languages do not sort lexicographically > but this is also besides the point [...] Totally agree. > Defining strings with this ordering means you can have some useful > ranges like [a-z], but then you cannot meaningfully use ranges for > things like [aardvark - zulu] --- note that in this particular example, > the order is reversed, because zulu comes before aardvark which is > probably not what you want. zzz.next = aaaa Yep. Its just useful to prove that the set of strings is countable (I wonder whether our world would be different if we had chosen the convention of looking up words in this "length+lexicographical" order in the first place -- it reminds me of how Chinese-speaking people look up symbols in a dictionary: number of strokes first). > In short, I think that while it is possible to define ranges of strings, > it is not as useful as one would like. Unless you are willing to treat strings as non-discrete, somehow. Regards - ---------------- [1] <http://en.wikipedia.org/wiki/Rational_numbers#Properties> - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLKbiFBcgs9XrR2kYRAnUYAJ0QBbWzNITA1KARLKdPTshFBSp/ZwCeIbin Lwc/pRBYgKoaccGpn0beyu4= =nViD -----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 17 Dec 2009 04:38 Tom Lane <tgl(a)sss.pgh.pa.us> writes: > Dimitri Fontaine <dfontaine(a)hi-media.com> writes: >> Tom Lane <tgl(a)sss.pgh.pa.us> writes: >>> foreach p2_member in unnest(p2) loop >>> p1 := array(select period_except(p1_member, p2_member) >>> from unnest(p1) p1_member); >>> end loop; >>> >>> But maybe it can be done in a single SQL command. > >> Yeah, as soon as you have LATERAL, I think. Without it there's no way to >> compose SRF in SQL, AFAIK. > > Hm, how would you do it with LATERAL? The problem is not so much > composition as the need for a variable number of rounds of > composition. Let's have a try at it: select p2_member, array_accum(p1) from unnest(p2) as p2_member lateral (select period_except(p1_member, p2_member) from unnest(p1) p1_member) as x(p1); I'm not sure I understand how the explicit looping over unnest(p2) is different from using lateral, or even if that's what you're talking about when mentioning variable number of rounds. Regards, -- dim -- 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: Tom Lane on 17 Dec 2009 09:43
Dimitri Fontaine <dfontaine(a)hi-media.com> writes: > Tom Lane <tgl(a)sss.pgh.pa.us> writes: >> Hm, how would you do it with LATERAL? The problem is not so much >> composition as the need for a variable number of rounds of >> composition. > Let's have a try at it: > select p2_member, array_accum(p1) > from unnest(p2) as p2_member > lateral (select period_except(p1_member, p2_member) > from unnest(p1) p1_member) as x(p1); I don't think that does it. Maybe I misunderstand LATERAL, but what that looks like to me is that each p1 will be separately filtered by each p2, giving rise to a distinct element in the output. What we need is for each p1 to be filtered by *all* p2's, successively (though in any order). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |