带有 IF ELSE 语句的 postgresql plpsql 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10473998/
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 plpsql function with IF ELSE statement
提问by Mihai Niculita
Suppose i have a table named t which is stored in a postgresql database. I have 6 columns named a, b, c, d, e, f. Columns a, b and c take values between 0 and 100, but on an aritmetic scale 0 < a < b < c < 100. Columns d, e and f take any value in the range 1 to 10.
假设我有一个名为 t 的表，它存储在 postgresql 数据库中。我有 6 列名为 a、b、c、d、e、f。a、b 和 c 列采用 0 到 100 之间的值，但在算术范围内 0 < a < b < c < 100。d、e 和 f 列采用 1 到 10 范围内的任何值。
I want to compute the weighted average of columns d, e and f, but with a condition related to columns a, b and c. The condition is that the average will be computed only on the a, b and c columns that have values smaller than 50.
我想计算列 d、e 和 f 的加权平均值，但条件与列 a、b 和 c 相关。条件是仅对值小于 50 的 a、b 和 c 列计算平均值。
I think this need a function so I started to do it:
我认为这需要一个函数，所以我开始这样做：
CREATE OR REPLACE FUNCTION example(t character varying, a character varying, b character varying, c character varying, d character varying, e character varying, f character varying, g character varying) RETURNS double precision AS $$
BEGIN
ALTER TABLE t ADD COLUMN g double precision;
UPDATE t
IF a > 50 THEN
SET g = d;
ELSE
IF b > 50 THEN;
SET g = (d+e)/2;
END IF c > 50 THEN
SET g = (d+e+f)/3;
END IF;
END;
$$ LANGUAGE plpgsql;
I get the following error:
我收到以下错误：
ERROR: syntax error at or near ""
LINE 1: ALTER TABLE ADD COLUMN double precision
^
QUERY: ALTER TABLE ADD COLUMN double precision
CONTEXT: SQL statement in PL/PgSQL function "example" near line 2
********** Error **********
ERROR: syntax error at or near ""
SQL state: 42601
Context: SQL statement in PL/PgSQL function "example" near line 2
Can someone tell me were I am wrong so I can go ahead with computing the needed average?
有人能告诉我我错了吗，所以我可以继续计算所需的平均值？
回答by Erwin Brandstetter
The immediate cause of the error are naming conflicts. You define parameters tand gand use the same names in your ALTER TABLEstatement. I make it a habit to prefix parameter names (like _t, _g) so they won't conflict with other names in the function body.
错误的直接原因是命名冲突。您定义参数t并g在您的ALTER TABLE语句中使用相同的名称。我习惯于为参数名称（如_t, _g）添加前缀，这样它们就不会与函数体中的其他名称发生冲突。
Also, your parameters should not be defined character varyingsince they hold numerical values. Use an appropriate numerical type, probably double precision.
此外，不应定义您的参数，character varying因为它们包含数值。使用适当的数值型，大概double precision。
But I don't think you need a function at all. This can be solved with plain SQL statements:
但我认为你根本不需要函数。这可以通过简单的 SQL 语句解决：
ALTER TABLE tbl ADD COLUMN g double precision;
UPDATE tbl
SET g = CASE
WHEN a > 50 THEN d
WHEN b > 50 THEN (d+e)/2
WHEN c > 50 THEN (d+e+f)/3
ELSE 0 -- definition for ELSE case is missing
END;
You could also scratch the whole idea completely and use a view for the purpose, as gonly holds functionally dependent data:
您也可以完全抓住整个想法并为此目的使用视图，因为g它只保存功能相关的数据：
CREATE VIEW tbl_with_g AS
SELECT *
, CASE
WHEN a > 50 THEN d
WHEN b > 50 THEN (d+e)/2
WHEN c > 50 THEN (d+e+f)/3
ELSE 0
END AS g
FROM tbl;
回答by kgrittn
I totally agree with everything in Erwin's answer, but want to point out one other option. You can create a sort of "generated column" which will be calculated on demand like this:
我完全同意 Erwin 回答中的所有内容，但想指出另一种选择。您可以创建一种“生成的列”，它将像这样按需计算：
CREATE FUNCTION g(rec t)
RETURNS double precision
IMMUTABLE
LANGUAGE SQL
AS $$
SELECT CASE
WHEN .a > 50 THEN .d
WHEN .b > 50 THEN (.d+.e)/2
WHEN .c > 50 THEN (.d+.e+.f)/3
ELSE 0
END;
$$;
You can then reference gjust as you would a column, as long as the reference is qualified by the table name or alias. For example:
然后您可以g像引用列一样引用，只要引用由表名或别名限定。例如：
SELECT *, t.g FROM t;
When an apparent qualified column reference doesn't resolve to a column, the PostgreSQL planner looks for a function by that name which takes the table's record type as its only parameter, and executes that function. Sometimes this approach is more convenient than using a view, although the effect is basically the same.
当一个明显的限定列引用没有解析为一个列时，PostgreSQL 规划器会寻找一个具有该名称的函数，该函数将表的记录类型作为其唯一参数，并执行该函数。有时这种方式比使用视图更方便，虽然效果基本相同。
