Adam Mackler-5 wrote > (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. > 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?
ISTM that if this was supported you would be doing it correctly. The main problem is you are abusing DOMAIN - which is strictly the base type with constraints - and trying to add operators specific to the DOMAIN (i.e., ones that would not work with the base type). And so now you have "domain = unknown" and the system is trying to figure out what unknown should be and also which operator to pick and it decides that since =(text,text) covers the domain and the unknown that is what it will pick. Maybe you should consider using an "enum" http://www.postgresql.org/docs/9.2/interactive/datatype-enum.html This doesn't solve the case insensitivity concern directly but you might be able to get the custom operator to work correctly on an enum where you apparently cannot with a DOMAIN. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/User-defined-operator-function-what-parameter-type-to-use-for-uncast-character-string-tp5813386p5813389.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
