How to use regex using ANY array operator

Question

I have a column containing an array of authors. How can I use the ~* operator to check if any of its values ​​match a given regular expression?

The ~* operator takes a string to test on the left and a regular expression to match on the right. The documentation says that the ANY operator should be on the right side, so obviously

`

 SELECT '^p' ~* ANY(authors) FROM book; 

`

It does not work, because PostgreSQL is trying to match the string ^p with the expressions contained in the array.

Any idea?

Answer 1

The first obvious idea is to use your own regular expression operator with variable arguments:

 create function commuted_regexp_match(text,text) returns bool as 'select $2 ~* $1;' language sql; create operator ~!@ # ( procedure=commuted_regexp_match(text,text), leftarg=text, rightarg=text ); 

Then you can use it as follows:

 SELECT '^p' ~!@ # ANY(authors) FROM book; 

---

Another way to look at it is to disable the array and formulate in SQL the equivalent of any construct:

 select bool_or(r) from (select author ~* '^j' as r from (select unnest(authors) as author from book) s1 ) s2; 

Answer 2

You can define your operator to do what you want.

Reverse the order of the arguments and call the corresponding function:

 create function revreg (text, text) returns boolean language sql immutable as $$ select texticregexeq($2,$1); $$; 

(revreg ... choose your favorite name).

Add a new statement using our revreg () function:

 CREATE OPERATOR ### ( PROCEDURE = revreg, LEFTARG = text, RIGHTARG = text ); 

Test:

  test=# SELECT '^p' ### ANY(ARRAY['ika', 'pchu']); t test=# SELECT '^p' ### ANY(ARRAY['ika', 'chu']); f test=# SELECT '^p' ### ANY(ARRAY['pika', 'pchu']); t test=# SELECT '^p' ### ANY(ARRAY['pika', 'chu']); t 

Note that you may need to set JOIN and RESTICT clauses for the new operator to help the scheduler.

Answer 3

I use this:

 create or replace function regexp_match_array(a text[], regexp text) returns boolean strict immutable language sql as $_$ select exists (select * from unnest(a) as x where x ~ regexp); $_$; comment on function regexp_match_array(text[], text) is 'returns TRUE if any element of a matches regexp'; create operator ~ ( procedure=regexp_match_array, leftarg=text[], rightarg=text ); comment on operator ~(text[], text) is 'returns TRUE if any element of ARRAY (left) matches REGEXP (right); think ANY(ARRAY) ~ REGEXP'; 

Then use it the same way you used ~ with text scalars:

 => select distinct gl from x where gl ~ 'SH' and array_length(gl,1) < 7; ┌──────────────────────────────────────┐ │ gl │ ├──────────────────────────────────────┤ │ {MSH6} │ │ {EPCAM,MLH1,MSH2,MSH6,PMS2} │ │ {SH3TC2} │ │ {SHOC2} │ │ {BRAF,KRAS,MAP2K1,MAP2K2,SHOC2,SOS1} │ │ {MSH2} │ └──────────────────────────────────────┘ (6 rows) 

Answer 4

My decision

 SELECT a.* FROM books a CROSS JOIN LATERAL ( SELECT author FROM unnest(authors) author WHERE author ~ E'p$' LIMIT 1 )b; 

Use a cross-side join, a subquery is evaluated for each row of the "books" table, if one of the rows returned by unsest matches the condition, the subquery returns one row (due to a constraint).

Answer 5

 SELECT * FROM book where EXISTS ( SELECT * from unnest(author) as X where x ~* '^p' )