CODEWITHSUNDEEP
postgresqlplpgsql
Panos Dimitrellos
Panos Dimitrellos

Reputation: 11

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

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?

Upvotes: 0

Views: 1434

Answers (1)

Panos Dimitrellos
Panos Dimitrellos

Reputation: 11

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;

Upvotes: 1

Related Questions