1

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';
1
  • isn't there a ; missing in the previous last END? Commented Apr 18, 2018 at 12:30

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.