Re: Range Types and extensions

On Jun12, 2011, at 04:37 , Robert Haas wrote:
> On Thu, Jun 9, 2011 at 6:26 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>> On Jun8, 2011, at 17:46 , Jeff Davis wrote:
>>> It looks like the type input function may be a problem, because it
>>> doesn't look like it knows what the collation is yet. In other words,
>>> PG_GET_COLLATION() is zero for the type input function.
>>>
>>> But I need to do a comparison to find out if the range is valid or not.
>>> For instance:
>>> '[a, Z)'::textrange
>>> is valid in "en_US" but not "C".
>>
>> Maybe that check should just be removed? If one views the range
>> '[L, U)' as a concise way of expressing "L <= x AND x < U" for some
>> x, then allowing the case L > U seems quite natural. There won't
>> be any such x of course, but the range is still valid, just empty.
>>
>> Actually, thinking for this a bit, I believe this is the only
>> way text ranges can support collations. If the validity of a range
>> depends on the collation, then changing the collation after creation
>> seems weird, since it can make previous valid ranges invalid and
>> vice versa.
>>
>> There could be a function RANGE_EMPTY() which people can put into
>> their CHECK constraints if they don't want such ranges to sneak
>> into their tables...
>
> I think the collation is going to have to be baked into the type
> definition, no? You can't just up and change the collation of the
> column as you could for a straight text column, if that might cause
> the contents of some rows to be viewed as invalid.

Now you've lost me. If a text range is simply a pair of strings,
as I suggested, and collations are applied only during comparison
and RANGE_EMPTY(), why would the collation have to be baked into
the type?

If you're referring to the case
(1) Create table with text-range column and collation C1
(2) Add check constraint containing RANGE_EMPTY()
(3) Add data
(4) Alter column to have collation C2, possibly changing
the result of RANGE_EMPTY() for existing ranges.
then that points to a problem with ALTER COLUMN.

best regards,
Florian Pflug