Re: [WIP] patch - Collation at database level

Tom Lane napsal(a):
> Gregory Stark <stark@enterprisedb.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>][
<padcharacteristic> ] [ <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