Re: Dynamic collation support

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2016-01-19 16:07 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>> For database functions, is there any way to manage collations besides
>> dynamic sql? It doesn't look like there is but I thought I'd ask.
>>
>> For example, I want to do something like:
>>
>> DO
>> $$
>> DECLARE
>> c name DEFAULT 'en_GB';
>> BEGIN
>> SELECT * FROM foo ORDER BY a COLLATE c;
>> END;
>> $$;
>>
>> and not
>>
>> DO
>> $$
>> DECLARE
>> c name DEFAULT 'en_GB';
>> BEGIN
>> EXECUTE 'SELECT * FROM foo ORDER BY a COLLATE ' || quote_ident(c);
>> END;
>> $$;
>>
>> I understand this would disable all index supported sorting; that's ok.
>>
>> Not being able to specify collation in a parameterized manner presents
>> some issues...I'm curious if there are workarounds.
>
>
> 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.

merlin