Re: Multilingual application, ORDER BY w/ different

--On Saturday, November 17, 2001 13:39:36 -0500 Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Palle Girgensohn <girgen(at)partitur(dot)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