PostgreSQL, at least until version 8.0, has rather weak support for various collating sequences. What you get when you do

select * from table order by column

is hardcoded in the database cluster at the initdb time. Yet, it is reasonable request to want one select to order by using English rules, another one to run with German rules and yet another with Czech ones, without having to dump, initdb, restore.

The distribution postgresql-nls-string-8.02.tar.gz (for PostgreSQL 8.0+) defines functions nls_string and nls_value which allow collation to be set at runtime:

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

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

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

The nls_value returns bytea while nls_string makes the result octal-encoded string.

Please, read the README.nls_string and INSTALL files included in the distribution.

If you use PostgreSQL server version 7.4, download postgresql-nls-string-0.53.tar.gz.

Author

Copyright: (c) 2004--2005 Jan Pazdziora.

All rights reserved. Permission to use, distribute, modify, an copy this software and this documentation for any purpose is hereby granted.