function - 带有 IF ELSE 语句的 postgresql plpsql 函数

Question

假设我有一个名为 t 的表，它存储在 postgresql 数据库中。我有 6 列，分别命名为 a、b、c、d、e、f。a、b 和 c 列采用 0 到 100 之间的值，但在算术尺度上 0 < a < b < c < 100。d、e 和 f 列采用 1 到 10 范围内的任何值。

我想计算列 d、e 和 f 的加权平均值，但条件与列 a、b 和 c 相关。条件是仅对值小于 50 的 a、b 和 c 列计算平均值。

我认为这需要一个函数，所以我开始做:

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;

我收到以下错误:

ERROR:  syntax error at or near "$1"
LINE 1: ALTER TABLE  $1  ADD COLUMN  $2  double precision
                 ^
QUERY:  ALTER TABLE  $1  ADD COLUMN  $2  double precision
CONTEXT:  SQL statement in PL/PgSQL function "example" near line 2

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "example" near line 2

有人可以告诉我我错了吗，以便我可以继续计算所需的平均值？

Answer 1

错误的直接原因是命名冲突。您定义参数 t 和 g 并在 ALTER TABLE 语句中使用相同的名称。我养成了给参数名称添加前缀的习惯(如 _t、_g)，这样它们就不会与函数体中的其他名称冲突。

此外，您的参数不应定义为character varying，因为它们包含数值。使用适当的 numerical type ，可能是 double 。

但我认为您根本不需要函数。这可以用普通的 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;

您也可以完全放弃整个想法并为此目的使用 View ，因为 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;