函数中的 PostgreSQL CASE 用法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8924035/
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
PostgreSQL CASE usage in functions
提问by Yohan Hirimuthugoda
Can't we use CASEcondition outside SQL SELECTstatements?
我们不能CASE在 SQLSELECT语句之外使用条件吗？
E.g.:
例如：
CASE
WHEN old.applies_to = 'admin' THEN _applies_to = 'My Self'
ELSE _applies_to = initcap(old.applies_to)
END
_summary = _summary || '<li>Apply To: ' || _applies_to || '</li>';
I get the following error:
我收到以下错误：
ERROR: syntax error at or near "_summary"
LINE 86: _summary = _summary || '<li>Apply To: ' || _applies ...
回答by Erwin Brandstetter
This concerns the conditional control structure CASEof the procedural language PL/pgSQL, to be used in plpgsql functionsor DOstatements. Not to be confused with the CASEexpression of SQL. Different language! And subtly different syntax rules, too.
这涉及在plpgsql 函数或语句中使用的过程语言 PL/pgSQL的条件控制结构CASE。不要与SQL的表达式混淆。不一样的语言！还有微妙不同的语法规则。DOCASE
While SQL CASEcan be embedded in SQL expressions inside PL/pgSQL code (which is mostly just glue for SQL commands), you cannot have stand-alone SQL CASEexpressions (would be nonsense).
虽然 SQLCASE可以嵌入到 PL/pgSQL 代码中的 SQL 表达式中（这主要是 SQL 命令的粘合剂），但您不能拥有独立的 SQLCASE表达式（这是无稽之谈）。
-- inside plpgsql code block:
CASE
WHEN old.applies_to = 'admin' THEN
_applies_to := 'My Self';
ELSE
_applies_to := initcap(old.applies_to);
END CASE;
You have to use fully qualified statements, terminated with semicolon (;) and END CASEto close it.
您必须使用完全限定的语句，以分号 ( ;)结尾并END CASE关闭它。
Answer to additional question in comment
回答评论中的其他问题
According to documentation the ELSEkeyword of a CASEstatement is notoptional. I quote from the link above:
根据文档，语句的ELSE关键字不是可选的。我引用上面的链接：CASE
If no match is found, the
ELSEstatements are executed; but ifELSEis not present, then aCASE_NOT_FOUNDexception is raised.
如果未找到匹配项，
ELSE则执行语句；但如果ELSE不存在，则会CASE_NOT_FOUND引发异常。
However, you can use an empty ELSE:
但是，您可以使用空的ELSE：
CASE
WHEN old.applies_to = 'admin' THEN
_applies_to := 'My Self';
ELSE
-- do nothing
END CASE;
This is different from SQL CASEexpressions where ELSEis optional, but if the keyword is present, an expression has to be given, too!
这与 SQLCASE表达式不同，whereELSE是可选的，但如果存在关键字，则也必须给出表达式！
