Início / dba / Perguntas / 196205
Accepted
exhuma
Asked: 2018-01-26 01:48:41 +0800 CST

Como posso usar uma função de 1 argumento em uma consulta "ANY"/"ALL"?

  • 772

Supondo que eu tenha a seguinte função:

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

Que retorna truese $1corresponder 'test'ou 'bar'.

Como posso usar essa função em outra ANYconsulta? Por exemplo:

 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')

Mas esta sintaxe está incorreta. E eu não sei muito bem como escrever isso corretamente.

editar: mais detalhes

Em essência, o que eu quero fazer é verificar se qualquer um de um conjunto de colunas contém qualquer um dos termos proibidos. Em outras palavras, tenho um conjunto de termos proibidos e preciso verificar várias colunas para conter essa palavra. Isso será usado posteriormente em uma política RLS. No exemplo abaixo, a função contains_forbidden_termé a função que eu gostaria de poder escrever.

Uma pequena, mas importante observação: As mesmas funções devem ser aplicadas a outras tabelas, mas com número variável de argumentos (daí o array como tipo de argumento).

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

2 respostas

  1. Best Answer

    Você pode usar a primeira função com, ANYmas precisa ter uma sintaxe válida como descobriu ( <expression> <**operator**> ANY ...). Portanto, o seguinte funcionaria (o "truque" é usar unnest()enquanto desdobra a matriz em linhas):

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

    ou o mais compacto:

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

  2. A partir dos documentos, parece que a sintaxe para ANY/ALLconsultas deve ser

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

    Continuei meu trabalho nessa direção e cheguei ao seguinte que funciona:

    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']);

    Mas isso parece um pouco como uma maneira indireta de fazer algo simples e me pergunto se não fui pego em uma toca de coelho enquanto usava antolhos, não vendo uma solução mais fácil (nota: também atualizei a pergunta com mais detalhes).

    • 1

relate perguntas