> Inventing commutator operators for LIKE etc could be a path of > much less resistance (unless the operator names get bikeshedded > to death). Are there really that many that people need? > A quick query of pg_operator suggests that the LIKE/regex family > is the bulk of the problem for real-world cases.
Sadly, I was afraid that might be the case. It would be so nice if it wasn't but if we have to go the path of least resistance. We'd need a standard for the reversed version of a command that didn't intersect with any other command or SQL keyword. We would also need a standard for the reversed version of operators that don't have a commutation that we want to support the opposite of. The only commands/operators that I think are probably really wanted in that category would be: * LIKE * ILIKE * ~ (regex match) * ~* (regex case insensitive match) * !~ (not regex match) * !~* (not regex case insensitive match) Proposed: (prefix the command/operator with ~ and put it before the ! if it's a "not" expression) * EKIL (LIKE backwards) or RLIKE or CLIKE or ~LIKE * EKILI (ILIKE backwards) or RILIKE or CILIKE or ~ILIKE * ~~ * ~~* or ~* * !~~ * !~~* or !~* or *~! On Wed, Oct 23, 2024 at 6:57 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Matthew Morrissette Vance <yinz...@gmail.com> writes: > > If instead, PostgreSQL could support the commutation of the `SOME/ANY` > and > > `ALL` operators so that the `ANY(array)` could be on both sides of the > > provided operator, it would allow for this kind of searching natively. > > > Firstly, would a PR that enhanced PostgreSQL in this manner be accepted? > > My gut feeling is you'll run into insurmountable grammar-ambiguity > problems. I might be wrong, but I have an idea that this has > already been tried and failed on that point. > > Inventing commutator operators for LIKE etc could be a path of > much less resistance (unless the operator names get bikeshedded > to death). Are there really that many that people need? > A quick query of pg_operator suggests that the LIKE/regex family > is the bulk of the problem for real-world cases. > > regards, tom lane >
