Re: Case-Insensitve Text Comparison

On Tue, Jun 03, 2008 at 01:53:56PM -0700, Jeff Davis wrote:
> On Tue, 2008-06-03 at 21:26 +0200, Zdenek Kotala wrote:
> > The first step is per database, because it is relative easy. Collation
> > per-column is very difficult. It requires a lot of changes (parser, planer,
> > executor...) in whole source code, because you need to keep collation
> > information together with text data.
>
> Right now typmod is already passed to all those layers, right? Would it
> be a useful intermediate step to use typmod to hold this information for
> the text type?

In SQL the collation is associated with a node in the parse tree and
not with the values at all. It's a sort of extra parameter to functions
(at least, that's how I implemented it). So you can say things like:

SELECT text COLLATE case_insensetive;

Here the collate clause does nothing, though if you had a SELECT INTO
it would control the default collation for that column. The standard
has rules on how to determine what the collation at any point (explicit
overrides implicit overrides default). If two columns have conflicting
collations, when comparing them you are required to disambiguate or
it's an (parse-time) error.

Check the archives for details on how it works precisely, but it's far
nicer than merely adding an typmod, since that would cause you to throw
errors at runtime if there's a problem.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.