Обсуждение: Dynamic collation support

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.

merlin

On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2016-01-19 16:07 GMT+01:00 Merlin Moncure <mmoncure@gmail.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

On Tue, Jan 19, 2016 at 9:42 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.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.

Hm, an extension wrapper to strxrm() might do the trick when
flexibility is favored over performance.  You'd have to constantly
mange the locale to make it work though.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.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.

            regards, tom lane

On Tue, Jan 19, 2016 at 11:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@gmail.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

On Tue, Jan 19, 2016 at 1:15 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> 2016-01-19 20:04 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>:
>>
>> On Tue, Jan 19, 2016 at 11:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> > Merlin Moncure <mmoncure@gmail.com> writes:
>> >> On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule
>> >> <pavel.stehule@gmail.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)
>
>
> this mechanism was used more time in Czech multilanguage applications
>
> Orafce.nlssort use it.
>
> https://github.com/orafce/orafce/blob/master/others.c

wow! that's perfect! -- thanks.

merlin