PostgreSQL 中的 IF 函数与 MySQL 中的一样
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12414683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IF function in PostgreSQL as in MySQL
提问by Pratyush
I am trying to replicate the IFfunction from MySQL into PostgreSQL.
我正在尝试将该IF函数从 MySQL复制到 PostgreSQL。
The syntax of IFfunction is IF(condition, return_if_true, return_if_false)
IF函数的语法是IF(condition, return_if_true, return_if_false)
I created following formula:
我创建了以下公式：
CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
RETURNS anyelement AS $$
BEGIN
CASE WHEN () THEN
RETURN ();
ELSE
RETURN ();
END CASE;
EXCEPTION WHEN division_by_zero THEN
RETURN ();
END;
$$ LANGUAGE plpgsql;
It works well with most of the things like if(2>1, 2, 1)but it raises an error for:
它适用于大多数事情，if(2>1, 2, 1)但它会引发以下错误：
if( 5/0 > 0, 5, 0)
fatal error division by zero.
致命错误除以零。
In my program I can't check the denominator as the condition is provided by user.
在我的程序中，我无法检查分母，因为条件是由用户提供的。
Is there any way around? Maybe if we can replace first parameter from boolean to something else, as in that case the function will work as it will raise and return the exception.
有什么办法吗？也许我们可以将第一个参数从 boolean 替换为其他参数，因为在这种情况下，该函数将起作用，因为它会引发并返回异常。
回答by Erwin Brandstetter
With a boolean argument, a division by zero will always throw an exception (and that's a good thing), beforeyour function is even called. There is nothingyou can do about it. It's already happened.
使用布尔参数，在您的函数被调用之前，除以零总是会抛出异常（这是一件好事）。有没有什么可以做的。它已经发生了。
CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
RETURNS anyelement LANGUAGE SQL AS
$func$
SELECT CASE WHEN THEN ELSE END
$func$;
I would strongly advise againsta function named ifto begin with. IFis a keyword in PL/pgSQL. If you use user defined functions written in PL/pgSQL this will be very confusing.
我强烈建议不要使用命名if为开头的函数。IF是 PL/pgSQL 中的关键字。如果您使用用 PL/pgSQL 编写的用户定义函数，这将非常令人困惑。
Just use the standard SQL expression CASEdirectly.
CASE直接使用标准的 SQL 表达式即可。
The alternative would be to take a textargumentand evaluate it with dynamic SQL.
另一种方法是采用一个text参数并使用动态 SQL对其进行评估。
Proof of concept
概念证明
What you ask for would work like this:
你要求的工作是这样的：
CREATE OR REPLACE FUNCTION f_if(_expr text
, _true anyelement
, _else anyelement
, OUT result anyelement)
RETURNS anyelement LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE '
SELECT CASE WHEN (' || _expr || ') THEN ELSE END' -- !! dangerous !!
USING _true, _else
INTO result;
EXCEPTION WHEN division_by_zero THEN
result := _else;
-- possibly catch more types of exceptions ...
END
$func$;
Test:
测试：
SELECT f_if('TRUE' , 1, 2) --> 1
,f_if('FALSE' , 1, 2) --> 2
,f_if('NULL' , 1, 2) --> 2
,f_if('1/0 > 0', 1, 2); --> 2
This is a big security hazardin the hands of untrusted users. Read @Craig's answer about making this more secure. However, I fail to see how it can be made bulletproof and would never use it.
这在不受信任的用户手中是一个很大的安全隐患。阅读@Craig's answer关于使这更安全。但是，我看不到它是如何防弹的，也永远不会使用它。
回答by Craig Ringer
PostgreSQL is following the standard
PostgreSQL 遵循标准
This behaviour appears to be specified by the SQL standard. This is the first time I've seen a case where it's a real problem, though; you usually just use a CASEexpression or a PL/PgSQL BEGIN ... EXCEPTIONblock to handle it.
此行为似乎是由 SQL 标准指定的。不过，这是我第一次看到这是一个真正问题的案例；您通常只使用CASE表达式或 PL/PgSQLBEGIN ... EXCEPTION块来处理它。
MySQL's default behaviour is dangerous and wrong. It only works that way to support older code that relies on this behaviour. It has been fixed in newer versionswhen strict modeis active (which it absolutely always should be) but unfortunately has not yet been made the default. When using MySQL, always enable STRICT_TRANS_TABLESor STRICT_ALL_TABLES.
MySQL 的默认行为是危险和错误的。它只能以这种方式支持依赖于这种行为的旧代码。当严格模式处于活动状态时（它绝对应该始终处于活动状态），它已在较新版本中得到修复，但不幸的是尚未成为默认模式。使用 MySQL 时，始终启用或.STRICT_TRANS_TABLESSTRICT_ALL_TABLES
ANSI-standard zero division is a pain sometimes, but it'll also protect against mistakes causing data loss.
ANSI 标准的零除法有时很麻烦，但它也可以防止错误导致数据丢失。
SQL injection warning, consider re-design
SQL注入警告，考虑重新设计
If you're executing expressions from the user then you quite likely have SQL injectionproblems. Depending on your security requirements you might be able to live with that, but it's pretty bad if you don't totally trust all your users. Remember, your users could be tricked into entering the malicious code from elsewhere.
如果您正在执行来自用户的表达式，那么您很可能会遇到SQL 注入问题。根据您的安全要求，您可能能够接受它，但如果您不完全信任所有用户，那就太糟糕了。请记住，您的用户可能会被诱骗从其他地方输入恶意代码。
Consider re-designing to expose an expression builder to the user and use a query builder to create the SQL from the user expressions. This would be much more complicated, but secure.
考虑重新设计以向用户公开表达式构建器，并使用查询构建器从用户表达式创建 SQL。这会复杂得多，但安全。
If you can't do that, see if you can parse the expressions the user enters into an abstract syntax, validate it before execution, and then produce new SQL expressions based on the parsed expression. That way you can at least limit what they can write, so they don't slip any nasties into the expression. You can also rewrite the expression to add things like checks for zero division. Finding (or writing) parsers for algebraic expressions isn't likely to be hard, but it'll depend on what kinds of expressions you need to let users write.
如果你不能这样做，看看你是否可以解析用户输入抽象语法的表达式，在执行之前对其进行验证，然后根据解析的表达式生成新的 SQL 表达式。这样你至少可以限制他们可以写的东西，所以他们不会在表达中加入任何讨厌的东西。您还可以重写表达式以添加诸如检查零除法之类的内容。查找（或编写）代数表达式的解析器可能并不难，但这取决于您需要让用户编写什么样的表达式。
At minimum, the app needs to be using a role ("user") that has only SELECTprivileges on the tables, is not a superuser, and does not own the tables. That'll minimise the harm any SQL injection will cause.
至少，应用程序需要使用一个角色（“用户”），该角色仅对SELECT表具有特权，不是超级用户，并且不拥有表。这将最大限度地减少任何 SQL 注入造成的危害。
CASE won't solve this problem as written
CASE 不会像所写的那样解决这个问题
In any case, because you currently don't validate and can't inspect the expression from the user, you can't use the SQL-standard CASEstatement to solve this. For if( a/b > 0, a, b)you'd usually write something like:
在任何情况下，因为您当前没有验证并且无法检查来自用户的表达式，所以您不能使用 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
This explicitly handles the zero denominator case, but is only possible when you can break the expression up.
这明确处理零分母的情况，但只有在您可以分解表达式时才有可能。
Ugly workaround #1
丑陋的解决方法 #1
An alternative solution would be to get Pg to return a placeholder instead of raising an exception for division by zero by defining a replacement division operator or function. This will only solve the divide-by-zero case, not others.
另一种解决方案是让 Pg 返回一个占位符，而不是通过定义替换除法运算符或函数来引发除以零的异常。这只会解决被零除的情况，而不是其他情况。
I wanted to return 'NaN'as that's the logical result. Unfortunately, 'NaN' is greater than numbers not less then, and you want a less-than or false-like result.
我想返回，'NaN'因为这是合乎逻辑的结果。不幸的是，'NaN' 大于数字而不是小于，并且您想要一个小于或类似错误的结果。
regress=# SELECT NUMERIC 'NaN' > 0;
?column?
----------
t
(1 row)
This means we have to use the icky hack of returning NULL instead:
这意味着我们必须使用返回 NULL 的 icky hack：
CREATE OR REPLACE FUNCTION div_null_on_zero(numeric,numeric) returns numeric AS $$
VALUES (CASE WHEN = 0 THEN NULL ELSE / END)
$$ LANGUAGE 'SQL' IMMUTABLE;
CREATE OPERATOR @/@ (
PROCEDURE = div_null_on_zero(numeric,numeric),
LEFTARG = numeric,
RIGHTARG = numeric
);
with usage:
用法：
regress=# SELECT 5 @/@ 0, 5 @/@ 0>0, CASE WHEN 5 @/@ 0 > 0 THEN 5 ELSE 0 END;
?column? | ?column? | case
----------+----------+------
| | 0
(1 row)
Your app can rewrite '/' in incoming expressions into @/@or whatever operator name you choose pretty easily.
您的应用程序可以@/@很容易地将传入表达式中的“/”重写为您选择的任何运算符名称。
There's one pretty critical problem with this approach, and that's that @/@will have different precedence to /so expressions without explicit parentheses may not be evaluated as you expect. You might be able to get around this by creating a new schema, defining an operator named /in that schema that does your null-on-error trick, and then adding that schema to your search_pathbefore executing user expressions. It's probably a bad idea, though.
这种方法有一个非常关键的问题，那就是它@/@具有不同的优先级，/因此没有显式括号的表达式可能不会按照您的预期进行计算。您可以通过创建一个新模式来解决这个问题，定义一个/在该模式中命名的运算符来执行您的 null-on-error 技巧，然后search_path在执行用户表达式之前将该模式添加到您的模式中。不过，这可能是个坏主意。
Ugly workaround #2
丑陋的解决方法 #2
Since you can't inspect the denominator, all I can think of is to wrap the whole thing in a DOblock(Pg 9.0+) or PL/PgSQL function and catch any exceptions from the evaluation of the expression.
由于您无法检查分母，我所能想到的就是将整个事物包装在一个DO块（Pg 9.0+）或 PL/PgSQL 函数中，并从表达式的评估中捕获任何异常。
Erwin's answerprovides a better example of this than I did, so I've removed this. In any case, this is an awful and dangerous thing to do, do not do it. Your app needs to be fixed.
Erwin 的回答提供了一个比我更好的例子，所以我删除了它。无论如何，这是一件可怕而危险的事情，千万不要这样做。您的应用需要修复。
