2

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;
  • You can use the original function with: SELECT (true = ANY (select myfun(x) from unnest(ARRAY['test', 'world']) as x )) ; – ypercubeᵀᴹ Jan 25 '18 at 10:46
  • Or this: SELECT bool_and(myfun(x)) from unnest(ARRAY['test', 'world']) as x ; – ypercubeᵀᴹ Jan 25 '18 at 10:51
  • Or SELECT true = ANY (select myfun(unnest(ARRAY['tt', 'foo']))) ; – ypercubeᵀᴹ Jan 25 '18 at 10:55
  • Consuming a set of values and returning a scalar result is essentially aggregation. Is it possible to create a custom aggregate function in PostgreSQL? – Andriy M Jan 25 '18 at 13:18
  • 1
    @AndriyM Yes it is (see postgresql.org/docs/9.5/static/xaggr.html). But I think the solution submitted by ypercube is maybe better to convey the "meaning" of the code. While the result is the same, semantically I am not aggregating anything. Rather I checck if anything matches. So future readers of the code might better understand the intent of that function. – exhuma Jan 25 '18 at 14:15
4

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']))) ;
  • 1
    This is way cleaner than what I came up with, and works like a charm. unnest was pretty much the puzzle-piece I was missing. Thanks a bunch ;) – exhuma Jan 25 '18 at 12:16
1

From the docs, it seems that the syntax for ANY/ALL queries must be

<expression> <**operator**> ANY ...

I continued my work in that direction and came up with the following which works:

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

CREATE OPERATOR <<< (
    PROCEDURE = myoperator,
    LEFTARG = boolean,
    RIGHTARG = TEXT
);

CREATE OR REPLACE FUNCTION contains_forbidden_term(IN TEXT[])
RETURNS boolean
LANGUAGE plpgsql
LEAKPROOF
IMMUTABLE
AS $CODE$
    BEGIN
    RETURN NOT (true <<< ANY($1));
    END
$CODE$;

SELECT true <<< ANY (ARRAY['foo', 'bar']);
SELECT true <<< ANY (ARRAY['this', 'is', 'ok']);

But this seems a bit like a roundabout way to do something simple and I wonder if I did not get caught up in a rabbit-hole while wearing blinders not seeing an easier solution (nb: I also updated the question with more detail).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.