How to use regular expression with 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 the string to check on the left and the regular expression to match on the right. The documentation says the ANY operator has to be on the right side so, obviously

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

does not work as PostgreSQL tries to match the string ^p against expressions contained in the array.

Any idea?

Answer 1

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

Answer 2

I use a generalization of Reece's approach:

select format($$
    create function %1$s(a text[], regexp text) returns boolean
    strict immutable language sql as
    %2$L;
    create operator %3$s (procedure=%1$s, leftarg=text[], rightarg=text);
    $$, /*1*/nameprefix||'_array_'||oname, /*2*/q1||o||q2, /*3*/oprefix||o
  )
from (values
        ('tilde'      , '~'  ), ('bang_tilde'      , '!~'  ),
        ('tilde_star' , '~*' ), ('bang_tilde_star' , '!~*' ),
        ('dtilde'     , '~~' ), ('bang_dtilde'     , '!~~' ),
        ('dtilde_star', '~~*'), ('bang_dtilde_star', '!~~*')
     ) as _(oname, o),
     (values
        ('any', '',  'select exists (select * from unnest(a) as x where x ', ' regexp);'),
        ('all', '@', 'select true = all (select x ', ' regexp from unnest(a) as x);')
     ) as _2(nameprefix, oprefix, q1, q2)
\gexec

Executing this in psql creates 16 functions and 16 operators that cover all applicable 8 matching operators for arrays -- plus 8 variations prefixed with @ that implement the ALL equivalent.

Very handy!