Re: Proposal - Collation at database level

Radek Strnad napsal(a):

<snip>

>
> I'm thinking of dividing the problem into two parts - in beginning
> pg_collation will contain two functions. One will have hard-coded rules
> for these basic collations (SQL_CHARACTER, GRAPHIC_IRV, LATIN1, ISO8BIT,
> UCS_BASIC). It will compare each string character bitwise and guarantee
> that the implementation will meet the SQL standard implemented in
> PostgreSQL.
>
> Second one will allow the user to use installed system locales. The set
> of these collations will obviously vary between systems. Catalogs will
> contain encoding and collation for calling the system locale function.
> This will allow us to use collations such as en_US.utf8, cs_CZ.iso88592
> etc. if they will be availible.
>
> We will also need to change the way how strings are compared. Regarding
> the set database collation the right function will be used.
> http://doxygen.postgresql.org/varlena_8c.html#4c7af81f110f9be0bd8eb2bd99525675
>
> This design will make possible switch to ICU or any other implementation
> quite simple and will not cause any major rewriting of what I'm coding
> right now.

Collation function is main point here. How you mentioned one will be only
wrapper about strcmp and second one about strcoll. (maybe you need four -
char/wchar) Which function will be used it is defined in pg_collation catalog by
CREATE COLLATION command. But you need specify name of locale for system
locales. It means you need attribute for storing locale name.

<snip>

> CATALOG(pg_collations, ###)
> {
> NameData colname; /* collation name */
> Oid colschema; /* collation schema */
> bool colpadattribute; /* pad attribute */
> bool colcasesensitive; /* case sensitive */
> bool colaccent; /* accent sensitive */
> regproc colfunc; /* used collation function */
> Oid colrepertoire; /* collation repertoire */
>
> } FormData_pg_collations;
>

It would be good to send list of new and modified SQL commands (like CREATE
COLLATION) for wide discussion.

Zdenek