Commutation of array SOME/ANY and ALL operators

From: Matthew Morrissette Vance <yinzara(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Commutation of array SOME/ANY and ALL operators
Date: 2024-10-24 01:27:59
Message-ID: CA+5VLd=L4kJ2im2uSkrCHSKsR7rDmrtc8Bkyrqe0US5PhNCRSg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michel Pelletier 2024-10-24 01:39:03 Re: Using Expanded Objects other than Arrays from plpgsql
Previous Message Masahiko Sawada 2024-10-24 00:54:39 Re: New "raw" COPY format