Howdy,
I'm working on a custom data type based on TEXT that does case-
insensitive, locale-aware comparisons, essentially by calling LOWER()
to compare values. I'll have more to ask about this later, when I want
to get feedback on the implementation. But right now I'm just writing
tests and trying to get it all to work the way I think it should.
So I've implemented operators and an operator class for the new type,
and they work great. I've also added implicit casts between the other
string data types:
CREATE CAST (lctext AS text) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (text AS lctext) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (lctext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (varchar AS lctext) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (lctext AS bpchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (bpchar AS lctext) WITHOUT FUNCTION AS IMPLICIT;
However, thanks to the implicit cast PostgreSQL finds more than one
candidate operator when I compare properly casted values:
try=# select 'a'::lctext = 'a'::text;
ERROR: operator is not unique: lctext = text
LINE 1: select 'a'::lctext = 'a'::text; ^
HINT: Could not choose a best candidate operator. You might need to
add explicit type casts.
So is there a way to resolve this? Would I need to add explicit
operators between lctext and text (and more, betwein text and lctext),
assuming that PostgreSQL would find those to be the best candidate
operators?
I'm kind of hoping that there's a simpler answer, because otherwise
I'd have to create operators and classes for all of:
( lctext, lctext ) ( lctext, text ) ( text, lctext ) ( lctext, lctext ) ( lctext, varchar ) (
varchar,lctext ) ( lctext, bpchar ) ( bpchar, lctext )
And then I supposed that I'd have to do the same not only for the
comparison operators in the operator class, but also any other binary
operators (concatenation, regular expression, LIKE, etc.). This sounds
like somewhat of a PITA, though I'd of course just do the cut-and-
paste work to make it so if that was what's required. But is it? Is
there no simpler way to do it?
Many thanks,
David
