Postgresql – How to use a 1-argument function in an “ANY”/“ALL” query

postgresql

Assuming I have the following function:

 CREATE OR REPLACE FUNCTION myfun(IN TEXT)
     RETURNS boolean
     LANGUAGE SQL
     IMMUTABLE
     LEAKPROOF
     AS $CODE$
         SELECT COALESCE(
             $1 ILIKE ANY (ARRAY[
                 'test',
                 'bar'
             ]),
             false
         );
     $CODE$;

Which returns true if $1 matches 'test' or 'bar'.

How can I use this function in another ANY query? For example:

 SELECT myfun ANY(ARRAY['hello', 'world']);  -- should return false
 SELECT myfun ANY(ARRAY['hello', 'foo']);  -- should return true ('foo' matches)
 SELECT myfun ANY(ARRAY['test', 'world']);  -- should return true ('test matches')

But this syntax is incorrect. And I don't quite know how to properly write this.

edit: more detail

In essence what I want to do is check if any one of a set of columns contains any one of forbidden terms. In other words, I have a set of forbidden terms, and have to check multiple columns for containment of that word. This will later be used in a RLS policy. In the example below, the function contains_forbidden_term is the function I would like to be able to write.

A small but important note: The same functions must be applied to other tables, but with varying number of arguments (hence the array as argument-type).

CREATE TABLE foo (
    name TEXT,
    last_name TEXT
);
CREATE USER bob;
ALTER TABLE foo ENABLE ROW LEVEL SECURITY;

CREATE POLICY bob_foo ON foo TO bob USING (contains_forbidden_term(ARRAY[name, last_name]));

GRANT SELECT ON foo TO bob;

Best Answer

You can use the first function with ANY but you have to have a valid syntax as you found out (<expression> <**operator**> ANY ...). So the following would work (the "trick" piece is to use unnest() whild unfolds the array into rows):

SELECT true = ANY (select myfun(x) from unnest(ARRAY['test', 'world']) as x) ;

or the more compact:

SELECT true = ANY (select myfun(unnest(ARRAY['tt', 'foo']))) ;
Related Question