主页 / dba / 问题 / 196205
Accepted
exhuma
Asked: 2018-01-26 01:48:41 +0800 CST

如何在“ANY”/“ALL”查询中使用 1 参数函数？

  • 772

假设我有以下功能：

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

true如果$1匹配则返回'test'or 'bar'。

如何在另一个ANY查询中使用此函数？例如：

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

但是这个语法是不正确的。而且我不太清楚如何正确地写这个。

编辑：更多细节

本质上我想要做的是检查一组列中的任何一个是否包含任何一个禁止的术语。换句话说，我有一组禁止的术语，并且必须检查多个列以包含该词。这稍后将用于 RLS 策略。在下面的示例中，函数contains_forbidden_term是我希望能够编写的函数。

一个小而重要的注意事项：必须将相同的函数应用于其他表，但参数数量不同（因此数组作为参数类型）。

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 个回答

  1. Best Answer

    您可以将第一个函数与一起使用，ANY但必须具有您发现的有效语法 ( <expression> <**operator**> ANY ...)。所以下面会起作用（“技巧”部分是使用unnest()whild 将数组展开成行）：

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

    或更紧凑的：

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

  2. 从文档来看，查询的语法似乎ANY/ALL必须是

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

    我继续朝着这个方向努力，并提出了以下可行的方法：

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

    但这似乎有点像做一些简单事情的迂回方式，我想知道我是否在戴着眼罩看不到更简单的解决方案时没有陷入兔子洞（注意：我还更详细地更新了问题）。

    • 1

相关问题