sql - PostgreSQL 中的 IF 函数与 MySQL 中一样

Question

我正在尝试复制 IF从 MySQL 到 PostgreSQL 的函数。
IF 的语法函数是IF(condition, return_if_true, return_if_false)
我创建了以下公式:

CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
   RETURNS anyelement AS $$
BEGIN
    CASE WHEN ($1) THEN
    RETURN ($2);
    ELSE
    RETURN ($3);
    END CASE;
    EXCEPTION WHEN division_by_zero THEN
    RETURN ($3);
END;
$$ LANGUAGE plpgsql;

它适用于大多数情况，例如 if(2>1, 2, 1)但它会引发错误:

if( 5/0 > 0, 5, 0)

fatal error division by zero.

在我的程序中，我无法检查分母，因为条件是由用户提供的。

有什么办法吗？也许如果我们可以将第一个参数从 bool 值替换为其他参数，因为在这种情况下，函数将工作，因为它会引发并返回异常。

Answer 1

PostgreSQL 遵循标准

此行为似乎是 specified by the SQL standard .不过，这是我第一次看到这是一个真正的问题的案例。您通常只使用 CASE表达式或 PL/PgSQL BEGIN ... EXCEPTION block 来处理它。

MySQL 的默认行为是危险和错误的。它只能以这种方式支持依赖此行为的旧代码。一直是fixed in newer versions当strict mode是事件的(它绝对应该是)，但不幸的是尚未设为默认值。使用 MySQL 时，请始终启用 STRICT_TRANS_TABLES或 STRICT_ALL_TABLES .

ANSI 标准的零除法有时很痛苦，但它也可以防止错误导致数据丢失。

SQL注入(inject)警告，考虑重新设计

如果您正在执行来自用户的表达式，那么您很可能有 SQL injection问题。根据您的安全要求，您可能可以接受，但如果您不完全信任所有用户，那就太糟糕了。请记住，your users could be tricked into entering the malicious code from elsewhere .

考虑重新设计以向用户公开表达式构建器并使用查询构建器从用户表达式创建 SQL。这将更加复杂，但安全。

如果做不到，请查看是否可以将用户输入的表达式解析为抽象语法，在执行前对其进行验证，然后根据解析的表达式生成新的 SQL 表达式。这样你至少可以限制他们可以写的东西，这样他们就不会在表达中加入任何讨厌的东西。您还可以重写表达式以添加诸如检查零除法之类的内容。为代数表达式查找(或编写)解析器可能并不难，但这取决于您需要让用户编写什么样的表达式。

应用程序至少需要使用只有 SELECT 的角色(“用户”)表的特权，不是 super 用户，并且不拥有表。这将最大限度地减少任何 SQL 注入(inject)将造成的危害。

CASE 不会像写的那样解决这个问题

在任何情况下，因为您当前不验证并且无法检查来自用户的表达式，所以您不能使用 SQL 标准 CASE声明来解决这个问题。对于 if( a/b > 0, a, b)你通常会写这样的东西:

CASE
    WHEN b = 0 THEN b
    ELSE CASE 
        WHEN a/b=0 THEN a
        ELSE b
    END
END

这显式地处理了零分母的情况，但只有当您可以分解表达式时才有可能。

丑陋的解决方法＃1

另一种解决方案是让 Pg 返回一个占位符，而不是通过定义替换除法运算符或函数来引发除以零的异常。这只会解决被零除的情况，而不是其他情况。

我想返回 'NaN'因为这是合乎逻辑的结果。不幸的是，'NaN' 大于数字而不是小于，并且你想要一个小于或类似错误的结果。

regress=# SELECT NUMERIC 'NaN' > 0;
 ?column? 
----------
 t
(1 row)

这意味着我们必须使用返回 NULL 的 icky hack 来代替:

CREATE OR REPLACE FUNCTION div_null_on_zero(numeric,numeric) returns numeric AS $$
VALUES (CASE WHEN $2 = 0 THEN NULL ELSE $1/$2 END)
$$ LANGUAGE 'SQL' IMMUTABLE;

CREATE OPERATOR @/@ (
    PROCEDURE = div_null_on_zero(numeric,numeric),
    LEFTARG = numeric,
    RIGHTARG = numeric
);

用法:

regress=# SELECT 5 @/@ 0, 5 @/@ 0>0, CASE WHEN 5 @/@ 0 > 0 THEN 5 ELSE 0 END;
 ?column? | ?column? | case 
----------+----------+------
          |          |    0
(1 row)

您的应用程序可以将传入表达式中的“/”重写为 @/@或您很容易选择的任何运营商名称。

这种方法有一个非常关键的问题，那就是 @/@将具有与 / 不同的优先级因此，没有显式括号的表达式可能不会按您的预期计算。您可以通过创建一个新模式、定义一个名为 / 的运算符来解决这个问题。在那个执行你的 null-on-error 技巧的模式中，然后将该模式添加到你的 search_path在执行用户表达式之前。不过，这可能是个坏主意。

丑陋的解决方法#2

由于您无法检查分母，我所能想到的就是将整个内容包装在 DO block 中。 (Pg 9.0+) 或 PL/PgSQL 函数并从表达式的评估中捕获任何异常。

Erwin's answer提供了一个比我更好的例子，所以我删除了这个。无论如何，这是一件可怕而危险的事情，不要这样做。您的应用程序需要修复。