Skip site navigation (1) Skip section navigation (2)

Re: Proposal - Collation at database level

From: Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM>
To: Radek Strnad <radek(dot)strnad(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal - Collation at database level
Date: 2008-05-29 15:21:29
Message-ID: 483EC9F9.8040608@sun.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Guillaume SmetDate: 2008-05-29 15:23:15
Subject: Re: Upcoming back-branch update releases
Previous:From: David FetterDate: 2008-05-29 15:21:05
Subject: Re: Core team statement on replication in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group