PostgreSQL: Check if each item in array is contained by a larger string

I have an array of strings in PostgreSQL:

SELECT ARRAY['dog', 'cat', 'mouse'];

And I have a large paragraph:

Dogs and cats have a range of interactions. The natural instincts of each species lead towards antagonistic interactions, though individual animals can have non-aggressive relationships with each other, particularly under conditions where humans have socialized non-aggressive behaviors.

The generally aggressive interactions between the species have been noted in cultural expressions.

For each item in the array, I want to check if it appears in my large paragraph string. I know for any one string, I could do the following:

SELECT paragraph_text ILIKE '%dog%';

But is there a way to simultaneously check every string in the array (for an arbitrary number of array elements) without resorting to plpgsql?

Solution

I belive you want something like this (assuming paragraph_text is column from table named table):

SELECT
    paragraph_text,
    sub.word,
    paragraph_text ILIKE '%' || sub.word || '%' as is_word_in_text
FROM
    table1 CROSS JOIN (
        SELECT unnest(ARRAY['dog', 'cat', 'mouse']) as word
    ) as sub;

Function unnest(array) takes creates table of record from array values. The you can do CROSS JOIN which means all rows from table1 are combines with all rows from that unnest-table.

If paragraph_text is some kind of static value (not from table) you can do just:

SELECT
    paragraph_text,
    sub.word,
    paragraph_text ILIKE '%' || sub.word || '%' as is_word_in_text
FROM (
         SELECT unnest(ARRAY['dog', 'cat', 'mouse']) as word
     ) as sub;