PostgreSQL - ERROR: syntax error at or near ";" END; In an IF-ELSE IF code

Question

I am quite new to PostgreSQL and trying to execute some queries. I have written this function in sql but i get an error when I try to execute the following code:

CREATE OR REPLACE FUNCTION title_basics_partitioner() 
RETURNS TRIGGER AS $$ 
BEGIN  
    IF (startyear is null) THEN
        INSERT INTO startyear_null VALUES (NEW.*);
    ELSE IF (startyear >= '1874' AND startyear < '1894') THEN
        INSERT INTO startyear_1874_1894 VALUES (NEW.*);
    ELSE IF (startyear >= '1894' AND startyear < '1914') THEN
        INSERT INTO startyear_1894_1914 VALUES (NEW.*);
    ELSE IF (startyear >= '1914' AND startyear < '1934') THEN
        INSERT INTO startyear_1914_1934 VALUES (NEW.*);
    ELSE IF (startyear >= '1934' AND startyear < '1954') THEN
        INSERT INTO startyear_1934_1954 VALUES (NEW.*);
    ELSE IF (startyear >= '1954' AND startyear < '1974') THEN
        INSERT INTO startyear_1954_1974 VALUES (NEW.*);
    ELSE IF (startyear >= '1974' AND startyear < '1994') THEN
        INSERT INTO startyear_1974_1994 VALUES (NEW.*);
    ELSE IF (startyear >= '1994' AND startyear < '2014') THEN
        INSERT INTO startyear_1994_2014 VALUES (NEW.*);
    ELSE IF (startyear >= '2014' AND startyear < '2115') THEN
        INSERT INTO startyear_2014_2115 VALUES (NEW.*);
    ELSE         
        RAISE EXCEPTION 'Out of range year value. Fix the title_basics_insert_trigger() function!';
    END IF;     
    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

And that's the error i get:

ERROR:  syntax error at or near ";"
LINE 26: END;
            ^
SQL state: 42601
Character: 1174

Any ideas?

I don't think you should add a semicolon after END. Did you try without it ? — Bidoubiwa
– Bidoubiwa, Commented Feb 1, 2020 at 0:03
@Couteau i just did and i got an other error on the next line with the "$$" at the beginning of the line. I also tried to put the semicolon at the end of "$$" but as before i got syntax error at the beginning of the next line.. — Panos Dimitrellos
– Panos Dimitrellos, Commented Feb 1, 2020 at 0:13
maybe it is newline sensitive, in which case could you try END; $$ (newline) LANGUAGE PLPGSQL; — Bidoubiwa
– Bidoubiwa, Commented Feb 1, 2020 at 0:19
There is a good example here : postgresql.org/docs/9.1/plpgsql-control-structures.html — Bidoubiwa
– Bidoubiwa, Commented Feb 1, 2020 at 0:22
@Couteau i found the problem , i changed all the "ELSE IF" to "ELSIF" and it created the function!! Thanks a lot for your time and your help, i appreciate it!!! — Panos Dimitrellos
– Panos Dimitrellos, Commented Feb 1, 2020 at 0:51

Panos Dimitrellos · Accepted Answer · 2020-02-01 01:20:37Z

The answer is to change the "ELSE IF" statement to "ELSIF"...

I also made some changes, i turned strings 'years' to ints ('1874' -> 1874) and i added NEW.startyear instead of simple startyear.

CREATE OR REPLACE FUNCTION title_basics_partitioner() 
RETURNS TRIGGER AS $$ 
BEGIN  
    IF (NEW.startyear is null) THEN
        INSERT INTO startyear_null VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1874 AND NEW.startyear < 1894) THEN
        INSERT INTO startyear_1874_1894 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1894 AND NEW.startyear < 1914) THEN
        INSERT INTO startyear_1894_1914 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1914 AND NEW.startyear < 1934) THEN
        INSERT INTO startyear_1914_1934 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1934 AND NEW.startyear < 1954) THEN
        INSERT INTO startyear_1934_1954 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1954 AND NEW.startyear < 1974) THEN
        INSERT INTO startyear_1954_1974 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1974 AND NEW.startyear < 1994) THEN
        INSERT INTO startyear_1974_1994 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 1994 AND NEW.startyear < 2014) THEN
        INSERT INTO startyear_1994_2014 VALUES (NEW.*);
    ELSIF (NEW.startyear >= 2014 AND NEW.startyear < 2115) THEN
        INSERT INTO startyear_2014_2115 VALUES (NEW.*);
    ELSE         
        RAISE EXCEPTION 'Out of range year value. Fix the title_basics_insert_trigger() function!';
    END IF;     
    RETURN NULL; 
END; 
$$
LANGUAGE plpgsql;

Collectives™ on Stack Overflow

PostgreSQL - ERROR: syntax error at or near ";" END; In an IF-ELSE IF code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related