Re: [WIP] patch - Collation at database level

Tom Lane napsal(a):
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> Out of curiosity, what is a "user-defined collation"? Are there SQL statements
>> to go around declaring what order code points should be sorted in? That seems
>> like it would be... quite tedious!
>
> Hm, that's a good point. SQL99 has
>
> <collation definition> ::=
> CREATE COLLATION <collation name> FOR
> <character set specification>
> FROM <existing collation name>
> [ <pad characteristic> ]
>
> <existing collation name> ::= <collation name>
>
> <pad characteristic> ::=
> NO PAD
> | PAD SPACE
>
> which seems pretty stupid if you ask me --- all the mechanism required
> to manage a new object type, just to enable PAD SPACE or not?
> (Especially when PAD SPACE itself is an utterly broken, useless concept
> ... but I digress.) You might as well just provide all the standard
> collations in both variants and be done with it.
>
> The statement looks the same in last year's 200n draft, so it's not
> like they were just about to add some more capability.

The proposed syntax of CREATE COLLATION is:

CREATE COLLATION <collation name> FOR <character set specification>
FROM <existing collation name> [STRCOLFN <fn name>]
[ <pad characteristic> ] [ <case sensitive> ] [ <accent sensitive> ]
[ LCCOLLATE <lc_collate> ] [ LCCTYPE <lc_ctype> ]

Which extends ANSI specification.

> We might be best off to treat collations like index access methods,
> ie, they're theoretically add-able but there's no infrastructure for
> managing them, and what's expected is that all the ones you need are
> created by initdb.

I think you cannot create all collation at bootstrap. You can only create record
for actual LC_COLLATION, because I there is not standard way how to obtain
complete list of supported collations and there is also problem if you install
new locales after initdb.

When I looked to another DB (MS SQL, MySQL, DB2, Firebird) then only Firebird
supports CREATE COLLATION command. Other databases has hard coded list of
locales. Hardcoded solution means to use some lib (e.g ICU) with unified names
or has locale name mapping for all supported OS.

I personally prefer open solution when I can create own collation and specify
collation function to handle it.

Zdenek