ANY/ALL modifiers for operators

[MHC2000]

Environment

• PostgreSQL version: 11.6
• PostgREST version: 7.02
• Operating system: SLES

Description of issue

Hi there,

I really like the work you do and the stable application you've builded.

In our application the user can search dynamically columns over freely choosen part-expressions and can combine expressions as alternating searchkeywords.

Currently I'm building the query by concatenating the alternating expressions by the or-Operator: or=(id.in.(1),title.ilike."*foo*",title.ilike."*bar*",title.ilike."*1*",description.ilike."*foo*",description.ilike."*bar*",description.ilike."*1*"). This can lead to quite long queries. I've already limited the length of the search expression, to prevent this, but still it looks odd to me, to add them up.

If I would do the query directly in postgres it would be something like this title ~* '(foo|bar|1)' or title ilike any (array['%foo%', '%bar%', '%1%']) As far as I know is IN the same as = ANY. I understand supporting regex-expressions would be a security risk, but maybe it would be possible to support like and ilike for the abbreviation .in or an alternative abrivation?

[steve-chavez]

@MHC2000 Hey there.

I was thinking we could add an operator for SIMILAR TO, which is more restricted than regex and also lets you group expressions like (foo|bar). But unfortunately SIMILAR TO is always case sensitive, so maybe not that useful.

If I would do the query directly in postgres it would be something like this title ~* '(foo|bar|1)' or title ilike any (array['%foo%', '%bar%', '%1%'])

I didn't knew about the ILIKE ANY variation. Since we already parse the ILIKE operand perhaps we can overload it and also accept an array like this:

GET /table?var=ilike.{*foo*,*bar*}

[MHC2000]

Hi @steve-chavez

I was thinking about SIMILAR TO at first too, but as you already mentioned, the missing possibility for case insensitivity wouldn't help completely.

Till yesterday I wasn't aware of the possibilities of combining ANY and LIKE/ILIKE myself. But it looks like ANY accepts everything which results in a boolean expression and works perfectly in Postgres.

The idea of overloading like and ilike to add the possibility for an array sounds interesting.

Please let me know if I can help in any way to make this feature possible. Was thinking about to try it myself but don't have any experience in Haskell, so I guess there won't be any results soon :-)

[wolfgangwalther]

Instead of doing a specific any construct for ilike only, I suggest to generalize this and add some kind of .any modifier (and .all at the same time: https://www.postgresql.org/docs/current/functions-comparisons.html) that can be used with every operator.

[steve-chavez]

@wolfgangwalther Interesting alternative. Not sure if it makes sense for all the operators we support but in addition to ILIKE it would also work with =,>,<:

select 3 >= all(array[2,1,3]);
┌──────────┐
│ ?column? │
├──────────┤
│ t        │
└──────────┘

So maybe we can add the any/all modifiers for some operators. e.g. col=eq.all.{2,1,3}, col=lt.any.{4,1,6}, etc.

[christiaanwesterbeek]

A great suggestion and proposal for the request formats! I like these:

GET /table?str=ilike.any.{*foo*,*bar*} GET /table?str=ilike.all.{*foo*,*bar*}

To build these queries:

select *
from table
where str ilike any ('{"%foo%", "%bar%"}');

select str
from table
where str ilike all ('{"%foo%", "%bar%"}');

[wolfgangwalther]

To add the modifier, we'd need to change the operator syntax as proposed in https://github.com/PostgREST/postgrest/issues/2066#issue-1070332545 (first bullet), I think.

[steve-chavez]

@wolfgangwalther Maybe we can just reuse the syntax of full text search(?my_tsv=fts(french).amusant ).

GET /table?str=ilike(any).{*foo*,*bar*}

GET /table?str=ilike(all).{*foo*,*bar*}

For FTS itself it could be:

GET /tsearch?my_tsv=fts(english,any).{fat,cat}