i'm trying to create a stored procedure accepting a variable number of input parameters, and according to this performing an insert or replace task. Since i'm working on version 9.0 of postgres i can't use an UPSERT as suggested here, instead, i'm using an if-then approach. The stored procedure is returning void.
I get error near last END of the stored procedure, right before $$, i think i'm missing a punctuation or something like this, i tried to add a semicolon between last two END but it didn't work.
The query is very long but the syntax error i get is probably trivial, i apologize but i'm a newcomer of postgres stored procedures world. I attach a "pseudocode" short version and the entire version.
Pseudocode version
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';
Full version
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';
The CASE statement should be closed with 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;
Read in the documentation about Control Structures.