Re: Multilingual application, ORDER BY w/ different locales?

Palle Girgensohn <girgen(at)partitur(dot)se> writes:
>> Actually, what the SQL spec suggests is that LOCALE be attached to
>> individual table columns. A SET command to cause LOCALE to change
>> on the fly within a session is quite impractical: that would mean
>> that the sort ordering of existing columns changes, which would mean
>> that any indexes on those columns are broken.

> OK, indexes and sort ordering are coupled, and must be?

Well, the sort ordering of any particular index has to be well-defined,
which means that there has to be a fixed locale associated with it.

> My need is really to get different sorting on *the same* column, depending
> on which locale the present user prefers.
> ... I guess this is not even supported by the SQL standard, or
> any other RDBMS for that matter, right?

I believe SQL regards the locale as essentially a property of a
datatype, which means that in theory you should be able to cast a column
value to type text-with-locale-X and then ORDER BY that. It'd be an
on-the-fly sort, not able to exploit any indexes, but it sounds like
that's acceptable to you.

Looking at the SQL92 spec, the name they actually give to this notion
is COLLATE, not locale, but it does look like you can label a string
expression with the collation type you want it to be sorted by.

regards, tom lane