Re: Multilingual application, ORDER BY w/ different

--On Saturday, November 17, 2001 13:39:36 -0500 Tom Lane 
<tgl@sss.pgh.pa.us> wrote:

> Palle Girgensohn <girgen@partitur.se> writes:
>> I am dreaming of a SET LC_COLLATE or simliar command that will
>> only affect my session, not all other users.
>> I know this is not implemented in postgres. How impossible is it to add
>> this feature, and what implications would pg suffer?
>
> 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? In that case, I see 
the problem.

> Per-column LOCALE is on the to-do list.

My need is really to get different sorting on *the same* column, depending 
on which locale the present user prefers. Collation can be quite different 
in Swedish, English, German or Frencn, for example. Our users can chose the 
language they prefer from a list, and since it is a web app, all languages 
are used simultaneously on the same system, and since we use a database 
session pool, different langs can be preferred att different times in the 
same database session. So, in this case there is no need for per-column 
locale; we really need to be able to shift sorting order (ORDER BY only) 
"on-the-fly". I guess this is not even supported by the SQL standard, or 
any other RDBMS for that matter, right?

> In my mind the main difficulty
> with it is that the standard C library doesn't really support concurrent
> use of multiple locales: it's built around the assumption that you set
> your locale once at program startup.  setlocale() is, typically, not
> a fast operation.  To get around this it seems we'd need to write our
> own set of locale library routines, which is a daunting amount of work.
>
> I think the last time this came up, someone mentioned that there's an
> open BSD-license locale library being worked on, which possibly we could
> adapt instead of reinventing this wheel for ourselves.  But I don't
> recall more than that.  Check the archives.

Thanks, I will.

Cheers,
Palle