Re: How to pass around collation information - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Re: How to pass around collation information
Date
Msg-id 1275069585.12068.17.camel@vanquo.pezone.net
Whole thread Raw
In response to Re: How to pass around collation information  (Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>)
Responses Re: How to pass around collation information
Re: How to pass around collation information
Re: How to pass around collation information
List pgsql-hackers
On fre, 2010-05-28 at 20:22 +0300, Heikki Linnakangas wrote:
> It's also fundamentally wrong, collation is not a property of a datum 
> but of the operation.

> One way to approach this is to realize that it's already possible to
> use 
> multiple collations in a database. You just have to define separate <
> = 
>  > operators and operator classes for every collation, and change all 
> your queries to use the right operator depending on the desired 
> collation everywhere where you use < = > (including ORDER BYs, with
> the 
> USING <operator> syntax). The behavior is exactly what we want, it's 
> just completely inpractical, so we need something to do the same in a 
> less cumbersome way.

Well, maybe we should step back a little and work out what sort of
feature we actually want, if any.  The feature I'm thinking of is what
people might call "per-column locale", and the SQL standard defines
that.  It would look like this:

CREATE TABLE test (   a varchar COLLATE de,   b varchar COLLATE fr
);

SELECT * FROM test WHERE a > 'baz' ORDER BY b;

So while it's true that the collation is used by the operations (> and
ORDER BY), the information which collation to use comes with the data
values.  It's basically saying, a is in language "de", so sort it like
that unless told otherwise.  There is also an override syntax available,
like this:

SELECT * FROM test WHERE a COLLATE en > 'baz' ORDER BY b COLLATE sv;

But here again the collation is attached to a data value, and only from
there it is passed to the operator.  What is actually happening is

SELECT * FROM test WHERE (a COLLATE en) > 'baz' ORDER BY (b COLLATE sv);


What you appear to be describing is a "per-operation locale", which also
sounds valid, but it would be a different thing.  It might be thought of
as this:

SELECT * FROM test WHERE a (> COLLATE en) 'baz' ORDER BY COLLATE sv b;

with some suitable global default.


So which one of these should it be?




pgsql-hackers by date:

Previous
From: Andy Balholm
Date:
Subject: Re: [BUGS] dividing money by money
Next
From: Bruce Momjian
Date:
Subject: Re: VPATH docs