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.
