I am trying to figure out which is the best way to store custom collation tables on a PostgreSQL server system, and what kind of interface to provide to users to allow them to create their own.
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'; The latter has the disadvantage that we'd need some smarts so that pg_dump would not repeat the mistakes that were made with dynamically loadable modules (such as absolute file paths). The former has the disadvantage that it is too unwieldy to be useful. We also need to consider the following two problems: Firstly, if the collation data -- no matter how it is created -- is stored within the database (that is, in some table(s)), then it would be duplicated in every database. Depending on the storage format, a collation table takes between around 100 kB and 800 kB. Multiply that by a few dozen languages, for each database. That would make an external file seem more attractive. (The external file would need to be a binary file that is precomputed for efficient processing, unless we want to reparse and reprocess it every so often, like for every session.) 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. Comments or better ideas? -- Peter Eisentraut [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
