Assume the following table structure for my examples.
CREATE TABLE my_table (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
int_array integer[],
text_array text[]
);
INSERT INTO my_table (id, int_array, text_array) VALUES
(1, '{5,10}', '{"1234%", "%5678"}'),
(2, '{8,15}', '{"1234", "5678"}');
The `SOME/ANY` and `ALL` operators are helpful in determining if a given
array element contains any/all values that match a provided expression.
The current operator works like:
SELECT * FROM my_table WHERE 5 = SOME(int_array);
This works great for the `=` operator and most other arithmetic operators
as the commutation of the operator is either the same or has a commuted
version of the operator (i.e. < >, >= <=, etc).
But for other operators like `LIKE` you can express:
SELECT * FROM my_table WHERE '12345789' LIKE ANY(text_array);
While this is helpful, that's the translation of:
find any value in the array that a provided value is `LIKE`
What if I instead what the translation of:
find if a provided value is like any element in the array.
i.e.:
SELECT * FROM my_table WHERE ANY(text_array) LIKE '1234%';
but you can't.
Because the `ANY/SOME` and `ALL` operators don't support the commutation of
the operator.
While this is technically possible using a custom defined operator that
reverses the arguments order, that requires that you create a custom
defined operator for every single type of operator you want to be able to
use with `ANY/ALL`. Any predicate function would require a custom operator
defined or a custom version of the function that reverses the operators to
be made.
There have been a number of issues raised around this:
https://stackoverflow.com/questions/34657669/find-rows-where-text-array-contains-value-similar-to-input
https://dba.stackexchange.com/questions/228235/match-string-pattern-to-any-array-element
https://dba.stackexchange.com/questions/117767/postgresql-pattern-match-against-array-elements
https://dba.stackexchange.com/questions/268884/postgres-having-ilike-any-wildcard-in-array-agg
https://stackoverflow.com/questions/55480064/how-to-search-in-array-with-like-operator
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?
Secondly, could anyone provide me a few tips of where I should start
looking at code to implement it?
