Arbitrary collation support for PostgreSQL
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.
If you use PostgreSQL server version 7.4, download postgresql-nls-string-0.53.tar.gz.
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.
- Karel Žák
- did the error checking for 7.4
- created the Makefile