a simple solution for locale per column

sometimes ago I asked the list if anybody has plan to add locale per column for postgresql or not. I concluded from replies that this need a lot of work.

 

Now we have a sugestion, the most important feature of multiple locale per column is that you can sort different columns based on different locale.

if we have a function similiar to nls_sort it can help us to that.

 

there is a function I attached to this mail. this function is similar to nls_sort.  this function is written by Karel Zak, I dont know if he had tried to submit this code or not.

 

How it works:

In this distribution you will find file nls_string.c. It contains the
definition of function nls_string(text, text) which takes a string
parameter and a locale name and returns string describing the ordering.
So you can run

  select * from table order by nls_string(name, 'en_US.UTF-8')

or

  select * from table order by nls_string(name, 'cs_CZ.UTF-8')

or

  select * from table order by nls_string(name, 'C')

and get what you expect -- the result is sorted the same way as it
would be with LC_COLLATE=locate sort on the command line.

Internally, the function sets the locale for LC_COLLATE category, runs
strxfrm on the first parameter and encodes the result as octal values.
Thus, it depends on your PostgreSQL collate setting (that which you
did upon initdb, you can check it with show lc_collate) to sort
numbers in the natural way. I believe this is reasonable assumption.

 

 

M. Taghizadeh