How to use regular expression with ANY array operator
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 much like you'd use ~ 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)
SELECT * FROM book where EXISTS ( SELECT * from unnest(author) as X where x ~* '^p' )
The first obvious idea is to use your own regexp-matching operator with commuted 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 may use it like this:
SELECT '^p' ~!@# ANY(authors) FROM book;
Another different way of looking at it to unnest the array and formulate in SQL the equivalent of the ANY construct:
select bool_or(r) from
(select author ~* '^j' as r
from (select unnest(authors) as author from book) s1
) s2;