how to do 'any(::text[]) ilike ::text'

Question

here is table structure

table1
pk int, email character varying(100)[]

data
1, {'[email protected]', '[email protected]', '[email protected]'}

what i try to achieve is find any 'gmail' from record

query
select * from table1 where any(email) ilike '%gmail%';

but any() can only be in left-side and unnest() might slow down performance. anyone have any idea?

edit

actually i kinda confuse a bit when i first post. i try to achieve through any(array[]).

this is my actual structure

pk int, 
code1 character varying(100), 
code2 character varying(100), 
code3 character varying(100), ...

my first approch is

select * from tabl1 where code1 ilike '%code%' or code2 ilike '%code%' or...

then i try

select * from table1 where any(array[code1, code2, ...]) ilike '%code%'

which is not working.

Answer 1

Create an operator that implements ILIKE "backwards", e.g.:

CREATE FUNCTION backward_texticlike(text, text) RETURNS booleans
    STRICT IMMUTABLE LANGUAGE SQL
    AS $$ SELECT texticlike($2, $1) $$;

CREATE OPERATOR !!!~~* (
    PROCEDURE = backward_texticlike,
    LEFTARG = text,
    RIGHTARG = text,
    COMMUTATOR = ~~*
);

(Note that ILIKE internally corresponds to the operator ~~*. Pick your own name for the reverse.)

Then you can run

SELECT * FROM table1 WHERE '%code%' !!!~~* ANY(ARRAY[code1, code2, ...]);