(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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general