Re: How to pass around collation information

On fre, 2010-05-28 at 14:48 -0400, Tom Lane wrote:
> > SELECT * FROM test WHERE a COLLATE en > 'baz' ORDER BY b COLLATE sv;
>
> That seems fairly bizarre. What does this mean:
>
> WHERE a COLLATE en > b COLLATE de
>
> ? If it's an error, why is this not an error
>
> WHERE a COLLATE en > b
>
> if b is marked as COLLATE de in its table?

The way I understand it, a collation "derivation" can be explicit or
implicit. Explicit derivations override implicit derivations. If in
the argument set of an operation, explicit collation derivations exist,
they must all be the same.

> I guess the more general question is whether the spec expects that
> collation settings can be derived statically (like type information)
> or whether they might sometimes only be known at runtime.

It looks like it is treated like type information. The derivation and
validation rules are part of the Syntax Rules.

> We also need to think about whether we're okay with only applying
> collation to built-in types (text, varchar, char) or whether we need
> the feature to work for add-on types as well. In particular, is citext
> still a meaningful feature if we have this, or is it superseded by
> COLLATE? In the abstract I'd prefer to let it work for user-defined
> types, but if we can have a much simpler implementation by not doing
> so, it might be better to give that up.

I think if we get this done using the strcoll_l() API to do the work,
which looks like the path of least resistance at the moment, citext
would still be useful because all the standard locales would still be
case sensitive.

> Is COLLATE a property that can be attached to a domain over text?

According to the spec, yes.