Re: Dynamic collation support

On Tue, Jan 19, 2016 at 11:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> Different collates requires different plans - so using dynamic SQL is much
>>> more correct.
>>> It is same like using variables as columns or tablenames.
>
>> Right -- I get it, and I understand the planner issues. But the
>> amount of revision that goes into a database that internationalizes
>> can be pretty large. To do it right, any static sql that involves
>> string ordering can't be used. pl/sql also can't be used. ISTM this
>> is impolite to certain coding styles.
>
> Well, it's the way the SQL committee specified collations to work, so
> we're pretty much stuck with that syntax.

I understand. It's water under the bridge if a strxfrm() wrapper
could deliver the goods here. Changing:

ORDER BY foo
to
ORDER BY strxfrm(foo, _CollationLocale)

is a nice escape route where _CollationLocale gets suddenly brought on
to the table. It's going to be awfully slow, but in many cases that's
acceptable. At least I think so -- I have to play with it.

merlin