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