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/55480064/how-to-search-in-array-with-like-operatorIf 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?
