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$;
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;