From: | Matthew Morrissette Vance <yinzara(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Commutation of array SOME/ANY and ALL operators |
Date: | 2024-10-24 09:55:01 |
Message-ID: | CA+5VLdmACJ=e0Q73VU4PrDW7WdUDWu2=9PcqKPa7+xN3b5R+bQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Matthew Morrissette Vance <yinzara(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2024-10-24 11:14:14 | Re: Pgoutput not capturing the generated columns |
Previous Message | Tender Wang | 2024-10-24 09:19:57 | Re: execute prepared statement passing parameter expression with COLLATE clause |