Re: Ideas needed: How to create and store collation tables

On Mon, 18 Nov 2002, Peter Eisentraut wrote:

> A collation table essentially consists of a mapping 'character code ->
> weight' for every character in the set and some additional considerations
> for one-to-many and many-to-one mappings, plus a few feature flags.
>
> How would a user go about creating such a table?
>
> CREATE COLLATION foo (
> ...
> <10000 lines of data>
> ...
> );
>
> or would it be preferrable to store the table in some external file and
> then have the call simply be, say,
>
> CREATE COLLATION foo SOURCE 'some file';

I'd say the latter makes more sense, but would it be better to use
CREATE COLLATION foo FROM EXTERNAL 'some file';
where we say valid implementation defined collation names are references
to files of the appropriate type?

> Secondly, because each collation table depends on a particular character
> encoding (since it is indexed by character code), some sort of magic needs
> to happen when someone creates a database with a different encoding than
> the template database. One option is to do some mangling on the
> registered external file name (such as appending the encoding name to the
> file name). Another option is to have the notional pg_collate system
> catalog contain a column for the encoding, and then simply ignore all
> entries pertaining to encodings other than the database encoding.

The SQL92 CREATE COLLATION seems to create a collation for a particular
character set, so the latter seems more appropriate to me, especially if
we plan to support the full range of SQL's character set/collation/padding
attributes at some point.