具有动态参数的 Postgres 存储过程中的语法错误

Question

我正在尝试创建一个接受可变数量的输入参数的存储过程，并据此执行插入或替换任务。由于我正在处理 postgres 的 9.0 版，因此我不能按照此处的建议使用 UPSERT，而是使用 if-then 方法。存储过程返回 void。

我在存储过程的最后一个 END 附近收到错误，就在 $$ 之前，我想我缺少标点符号或类似的东西，我试图在最后两个 END 之间添加一个分号，但它不起作用。

查询很长，但我得到的语法错误可能微不足道，我很抱歉，但我是 postgres 存储过程世界的新手。我附上一个"伪代码"短版本和整个版本。

伪代码版本

CREATE OR REPLACE FUNCTION function(
IN parameter,
...
)
RETURNS void AS
$$
BEGIN   
CASE 
WHEN(parameters) IS NULL THEN
IF condition
THEN
UPDATE ...;
ELSE 
INSERT ...;
END IF;
WHEN ...
ELSE
IF condition
THEN
UPDATE ...;
ELSE 
INSERT ...;
END IF;
END
END;
$$ 
LANGUAGE 'plpgsql';

完整版

CREATE OR REPLACE FUNCTION insert_peronospora (
IN _id_stazione  int,
IN _lw_threshold numeric,
IN _starting_condition_time   timestamp,
IN _phenologic_condition_time timestamp = NULL,
IN _germination_time timestamp = NULL,
IN _infestation_percentage numeric = NULL,
IN _infestation_time timestamp = NULL,
IN _secondary_infestation_time timestamp = NULL,
IN _secondary_infestation_number int = NULL
)
RETURNS void AS
$$
BEGIN   
CASE 
WHEN(_phenologic_condition_time, _germination_time, _infestation_percentage, _infestation_time, _secondary_infestation_time, _secondary_infestation_number) IS NULL THEN
IF EXISTS (SELECT id_stazione FROM dss_vite.peronospora WHERE id_stazione = _id_stazione and lw_threshold = _lw_threshold)
THEN
update dss_vite.peronospora 
set starting_condition_time = _starting_condition_time 
where id_stazione= _id_stazione and lw_threshold = _lw_threshold;
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time) VALUES(_id_stazione, _lw_threshold, _starting_condition_time);
END IF;
WHEN(_germination_time, _infestation_percentage, _infestation_time, _secondary_infestation_time, _secondary_infestation_number) IS NULL THEN
IF EXISTS (SELECT id_stazione FROM dss_vite.peronospora WHERE id_stazione = _id_stazione and lw_threshold = _lw_threshold)
THEN
update dss_vite.peronospora 
set starting_condition_time = _starting_condition_time,
phenologic_condition_time = _phenologic_condition_time
where id_stazione= _id_stazione and lw_threshold = _lw_threshold;
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time, phenologic_condition_time) 
VALUES(_id_stazione, _lw_threshold, _starting_condition_time, _phenologic_condition_time);
END IF;
WHEN(_infestation_percentage, _infestation_time, _secondary_infestation_time, _secondary_infestation_number) IS NULL THEN
IF EXISTS (SELECT id_stazione FROM dss_vite.peronospora WHERE id_stazione = _id_stazione and lw_threshold = _lw_threshold)
THEN
update dss_vite.peronospora 
set starting_condition_time = _starting_condition_time,
phenologic_condition_time = _phenologic_condition_time,
germination_time = _germination_time
where id_stazione= _id_stazione and lw_threshold = _lw_threshold;
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time, phenologic_condition_time, germination_time) 
VALUES(_id_stazione, _lw_threshold, _starting_condition_time, _phenologic_condition_time, _germination_time);
END IF;
WHEN(_infestation_time, _secondary_infestation_time, _secondary_infestation_number) IS NULL THEN
IF EXISTS (SELECT id_stazione FROM dss_vite.peronospora WHERE id_stazione = _id_stazione and lw_threshold = _lw_threshold)
THEN
update dss_vite.peronospora 
set starting_condition_time = _starting_condition_time,
phenologic_condition_time = _phenologic_condition_time,
germination_time = _germination_time,
infestation_percentage = _infestation_percentage
where id_stazione= _id_stazione and lw_threshold = _lw_threshold;
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time, phenologic_condition_time, germination_time, infestation_percentage) 
VALUES(_id_stazione, _lw_threshold, _starting_condition_time, _phenologic_condition_time, _germination_time, _infestation_percentage);
END IF;
WHEN(_secondary_infestation_time, _secondary_infestation_number) IS NULL THEN
IF EXISTS (SELECT id_stazione FROM dss_vite.peronospora WHERE id_stazione = _id_stazione and lw_threshold = _lw_threshold)
THEN
update dss_vite.peronospora 
set starting_condition_time = _starting_condition_time,
phenologic_condition_time = _phenologic_condition_time,
germination_time = _germination_time,
infestation_percentage = _infestation_percentage,
infestation_time = _infestation_time
where id_stazione= _id_stazione and lw_threshold = _lw_threshold;
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time, phenologic_condition_time, germination_time, infestation_percentage,infestation_time) 
VALUES(_id_stazione, _lw_threshold, _starting_condition_time, _phenologic_condition_time, _germination_time, _infestation_percentage, _infestation_time);
END IF;
ELSE
IF EXISTS (SELECT id_stazione FROM dss_vite.peronospora WHERE id_stazione = _id_stazione and lw_threshold = _lw_threshold)
THEN
update dss_vite.peronospora 
set starting_condition_time = _starting_condition_time,
phenologic_condition_time = _phenologic_condition_time,
germination_time = _germination_time,
infestation_percentage = _infestation_percentage,
infestation_time = _infestation_time,
secondary_infestation_time = _secondary_infestation_time,
secondary_infestation_number = _secondary_infestation_number
where id_stazione= _id_stazione and lw_threshold = _lw_threshold;
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time, phenologic_condition_time, germination_time, infestation_percentage,infestation_time, secondary_infestation_time,secondary_infestation_number) 
VALUES(_id_stazione, _lw_threshold, _starting_condition_time, _phenologic_condition_time, _germination_time, _infestation_percentage, _infestation_time,_secondary_infestation_time, _secondary_infestation_number);
END IF;
END
END;
$$ 
LANGUAGE 'plpgsql';

Answer 1

CASE语句应以END CASE

...
ELSE 
INSERT INTO dss_vite.peronospora (id_stazione, lw_threshold, starting_condition_time, phenologic_condition_time, germination_time, infestation_percentage,infestation_time, secondary_infestation_time,secondary_infestation_number) 
VALUES(_id_stazione, _lw_threshold, _starting_condition_time, _phenologic_condition_time, _germination_time, _infestation_percentage, _infestation_time,_secondary_infestation_time, _secondary_infestation_number);
END IF;
END CASE;
END;

阅读有关控制结构的文档。