Re: OCTET_LENGTH is wrong

Tom Lane wrote:
> 
> Tatsuo Ishii  writes:
> > Yes. Consider you have UNICODE database and want to sort by French or
> > whatever LATIN locale.
> >        SELECT * FROM t1 ORDER BY convert(text_column,'LATIN1');
> > would be the only way to accomplish that.
> 
> That in itself would not get the job done; how is the sort operator
> to know what collation order you want?
> 
> The SQL92 spec suggests that the syntax should be
> 
>         ... ORDER BY text_column COLLATE French;
> 
> (note collation names are not standardized AFAICT).  Seems to me it
> should then be the system's responsibility to make this happen,
> including any encoding conversion that might be needed before the
> comparisons could be done.

Thanks to postgreSQL's flexibility you can currently make a contrib 
function convert(text_column,'LATIN1',locale) that returns a (new) 
text_with_locale type that has locale_aware comparison operators.

--------------
Hannu