On Wed, Sep 14, 2005 at 10:42:36PM -0400, Tom Lane wrote:
> The thing that's still fairly unclear to me is whether the collation
> information is attached to the operators/functions or to the data.
> I recall there's been some discussion of sticking collation IDs into
> individual text Datums, which is a completely different path than what
> you are positing above. Does the SQL spec mandate one or the other of
> these approaches? If it does, do we want to believe it? (The more I
> read of SQL2003, the less impressed I get...)
The standard doesn't care I think. My reading is that the COLLATE
status is determined at parse time. From there you can plan however you
like.
AFAIUI, collate is a parameter/property of fields and domains and
affects operators and function, not the data. It only applies to
comparisons, not the output. You could add it as a property to the
data. I wrote a module, taggedtypes [1], which basically implemented
this. My main issue with it is that for the '<' operator, the same
collate property has to be on both arguments or it has to bail. The
only reason why you can attach COLLATE to fields and domains is to give
a default in case the user doesn't specify anything. But if the COLLATE
is given explicitly, it overrides anything.
By way of example:
CREATE TABLE t (a text collate c1, b text collate c2);
select * from t where a < b; -- ERROR: Indeterminate collate
select * from t where a < b COLLATE c3; -- Order by c3
My worry about adding the collate to the Datum is that your execution
tree becomes more complex. The two types, with and without COLLATE data
are not interchangable and you would have to add or remove them
continuously.
In the above example, you would have to create an executor node whose
sole purpose is to add the collate bit to a and b before passing it to
the '<' operator. An index can only support one collation at a time
also.
I don't think there is an easy way out...
[1] http://svana.org/kleptog/pgsql/taggedtypes.html
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.