(Cross-posted to StackOverflow:
http://stackoverflow.com/questions/25041100/postgresql-user-defined-operator-function-what-parameter-type-to-use-for-uncast
)
I'm defining my own domain and a equality operator. I cannot cause
PostgreSQL to use my operator function in a query without explicitly
casting a character-string argument to the type I used in defining the
operator. My question is how to cause my custom operator to be used
without the cast.
As an example: first I define a domain and a table column of that
type. For this example, the type requires its values to be three
uppercase letters A, B or C, and the equality operator will cause
queries to match regardless of case.
CREATE domain my_domain as char(3) check(VALUE similar to '[A-C]{3}');
CREATE TABLE my_table (val my_domain);
INSERT INTO my_table VALUES ('ABC');
The type of the column is my_domain:
sandbox=> \d my_table
Table "public.my_table"
Column | Type | Modifiers
--------+-----------+-----------
val | my_domain |
Before defining the custom equality operator, case-sensitive queries
work as I expect. The row in the table is capital letters, so the
query must contain capital letters to match the row
sandbox=> SELECT * FROM my_table WHERE val='abc';
val
-----
(0 rows)
sandbox=> SELECT * FROM my_table WHERE val='ABC';
val
-----
ABC
(1 row)
Next I create an equality operator to do case-insensitive matching:
CREATE FUNCTION my_equals(this my_domain, that text) RETURNS boolean AS
'SELECT CAST (this AS text) = upper(that)' LANGUAGE SQL;
CREATE OPERATOR = (procedure=my_equals, leftarg=my_domain, rightarg = text);
The new operator is invoked causing a query containing lowercase
letters to match the uppercase column value, but only if I cast the
type of the WHERE clause:
sandbox=> SELECT * FROM my_table WHERE val=CAST ('abc' AS text);
val
-----
ABC
(1 row)
sandbox=> SELECT * FROM my_table WHERE val='abc';
val
-----
(0 rows)
Question: What can I do so my custom equality operator is used without
the cast? In other words, how to cause the last query above return
the table row (without changing the query)? I have tried defining
my_equals() so its second parameter type is either varchar and
char(3), but those still require a cast in the WHERE-clause of the
query. I've also tried anyelement, but that does not work even with a
cast.
Thank you,
--
Adam Mackler
