On Tue, Jan 19, 2016 at 9:15 AM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > 2016-01-19 16:07 GMT+01:00 Merlin Moncure <mmoncure@xxxxxxxxx>: >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
