Re: OCTET_LENGTH is wrong

Tom Lane wrote:
>
> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> 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