On Sat, Mar 26, 2011 at 3:16 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> ** What to do with domains whose declaration includes a COLLATE clause?
>> Currently, we'll impute that collation to the result of a cast to the
>> domain type --- even if the cast's input expression includes an
>> explicit COLLATE clause.
>
> I would have thought that an explicit COLLATE clause would trump any action at a distance.
>
I think an explicit COLLATE *outside* the cast would. But inside the
cast? The question comes down to whether a domain with a collate
clause is explicitly providing a collation or implicitly.
So again, examples:
CREATE DOMAIN name AS text COLLATE english;
CREATE DOMAIN countrycode AS char(2) COLLATE C;
1) SELECT * from users where country = 'DE' order by first_name COLLATE german;
2) SELECT * from users where country = 'DE' order by
(tolower(first_name) COLLATE german)::name;
3) SELECT * from users order by substr(address,1,2)::countrycode COLLATE english
4) SELECT * from users order by (substr(address,1,2) COLLATE
english)::countrycode
The ones with the collation expressions inside the casts seem very
strange and the behaviour following the domain don't seem
unreasonable. The behaviour with the collate clauses outside the cast
should definitely be follow the explicit collate clause.
--
greg