PostgreSQL中的IF函数和MySQL一样
|
我试图将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)
在我的程序中,我无法检查分母,因为条件是由用户提供的. 有什么办法吗？也许如果我们可以将第一个参数从布尔值替换为其他东西,因为在这种情况下函数将起作用,因为它将引发并返回异常.
PostgreSQL遵循标准
这种行为似乎是specified by the SQL standard.这是我第一次看到这是一个真正的问题的案例;你通常只使用CASE表达式或PL / PgSQL BEGIN … EXCEPTION块来处理它. MySQL的默认行为是危险和错误的.它只能以这种方式支持依赖于此行为的旧代码.当strict mode处于活动状态时(它绝对应该是),它已经是fixed in newer versions,但遗憾的是还没有成为默认值.使用MySQL时,始终启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES. ANSI标准的零分割有时会很痛苦,但它也可以防止导致数据丢失的错误. SQL注入警告,考虑重新设计 如果您正在执行用户的表达式,那么很可能会遇到SQL injection问题.根据您的安全要求,您可以使用它,但如果您不完全信任所有用户,那将非常糟糕.请记住,your users could be tricked into entering the malicious code from elsewhere. 考虑重新设计以向用户公开表达式构建器,并使用查询构建器从用户表达式创建SQL.这将更复杂,但更安全. 如果您不能这样做,请查看是否可以解析用户输入的表达式为抽象语法,在执行前验证它,然后根据解析的表达式生成新的SQL表达式.这样你至少可以限制他们可以写的东西,所以他们不会把任何恶意搞砸到表达中.您还可以重写表达式以添加检查零分割等内容.为代数表达式查找(或编写)解析器可能不是很难,但它取决于您需要让用户编写什么类型的表达式. 至少,应用程序需要使用一个角色(“用户”),该角色对表只有SELECT权限,不是超级用户,并且不拥有这些表.这将最小化任何SQL注入将导致的伤害. 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)
您的应用程序可以将传入表达式中的“/”重写为@ / @或您轻松选择的任何运算符名称. 这种方法存在一个非常严重的问题,那就是@ / @将具有不同的优先级/ so表达式而没有明确的括号可能无法按预期进行评估.您可以通过创建新模式来解决这个问题,在该模式中定义名为/的运算符,该运算符执行您的null-on-error技巧,然后在执行用户表达式之前将该模式添加到search_path.不过,这可能是一个坏主意. 丑陋的解决方法#2 由于你无法检查分母,我所能想到的就是将整个事物包装在 Erwin’s answer提供了一个比我更好的例子,所以我删除了它.无论如何,这是一件非常危险的事情,不要这样做.您的应用需要修复. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |