Re: Re: User-defined operator function: what parameter type to use for uncast character string?

On Wed, Jul 30, 2014 at 10:59:28PM -0700, David G Johnston wrote:
> ISTM that if this was supported you would be doing it correctly.

Thank you for the quick response. I'm not understanding you. Could you elaborate?

> 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).

Can you explain what the abuse is? Also why the "=" operator does not
work even without the domain? If I do everything the same, but make
the column type "char(3)" rather than "my_domain", then still I need
to cast the literal in the query to "text". I'm not understanding (1)
why postgres doesn't use the type of the column--either char(3) or
my_domain--and then choose the operator function that has that type
for its first paramater even where the second is "unknown", and (2)
why using the cast "WHERE val='abc'::text" makes it work. It seems as
if the determining factor is the type of the literal in the
WHERE_clause.

> 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.

If =(text,text) is chosen where the second argument is "unknown"
rather than "text", then postgres has no problem deciding that a
parameter defined for "text" will handle "unknown", right? So if I
define =(my_domain,text) or =(char(3),text) then wouldn't those be
preferable to =(text,text) where the first argument type can be known
from the type of the column (as defined in the table) used as the
first argument to the operator function?

If I give my operator the unique name "~~~~" then my operator function
is chosen without the cast. That makes it seem like a matter of
priority, where the built-in "=" operator takes priority over my
user-defined one. If postgres will accept my operator as being
appropriate, isn't there some way to give it priority over the built-in
=(text,text) operator?

In other words, "\d my_table" shows the type of the column as
"my_domain" or "char(3)" depending on how I define it. So why isn't
that taken into acount when choosing the operator function when the
second argument is "unknown"?

> Maybe you should consider using an "enum"

I don't see how I can use an enum. Wouldn't that require defining all
possible cominations, which even in this limited example would be 26^3
values? I just used three uppercase letters (and case-insensitive
matching) as an example. In my actual application I have twelve
characters and the operator function is doing more advanced
regular-expression matching.

Thanks again,
--
Adam Mackler