Creating a function. Error: syntax error at or near ";" OR Error: No language specified

Question

So I am creating a function in PostgreSQL for the first time and I am having a problem with the syntax or where exactly to put semicolons;

The function looks something like this:

CREATE OR REPLACE FUNCTION func(name1 TEXT, int1 INTEGER) RETURNS TEXT AS $$

DECLARE new_name TEXT;
DECLARE int2 INTEGER;

BEGIN

...
Some SELECT FROM table queries
...

IF int2 < int1 THEN
    new_name := func(new_name, int1);
ELSE IF int2 = int1 THEN
    RETURN new_name;
ELSE
    RETURN name1;
END IF;

END;

$$ LANGUAGE plpgsql;

If I try to submit this I get

ERROR:  syntax error at or near ";"
LINE 24: END;
            ^

If I remove the semicolon after END I get

ERROR:  syntax error at end of input
LINE 26: $$ LANGUAGE plpgsql;
         ^

And if I remove the semicolon after END and insert one after the $$ I get

$$; LANGUAGE plpgsql;
ERROR:  no language specified
ERROR:  syntax error at or near "LANGUAGE"
LINE 1: LANGUAGE plpgsql;
        ^

Found the problem. Should have been ELSEIF instead of ELSE IF

You should provide the answer you have found by supplying it as an answer, that way its clear — SingleNegationElimination
– SingleNegationElimination, Commented Mar 31, 2014 at 3:33
It's ELSIF not ELSE IF postgresql.org/docs/current/static/… — user330315
– user330315, Commented Mar 31, 2014 at 19:42

Senju · Accepted Answer · 2015-09-08 22:55:06Z

3

Found the problem:

Should have been ELSEIF instead of ELSE IF.

edited Sep 8, 2015 at 22:55

answered Mar 31, 2014 at 18:06

Senju

1,04510 silver badges26 bronze badges

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

Comments

Burg · Accepted Answer · 2014-03-31 01:04:09Z

0

You're going to want to remove all but the first DECLARE statement.

CREATE OR REPLACE FUNCTION func(name1 TEXT, int1 INTEGER) RETURNS TEXT AS $$

DECLARE
new_name TEXT;
int2 INTEGER;
BEGIN
...
END;

$$ LANGUAGE plpgsql;

I'd also highly recommend that you prefix your variables and parameters with something lik v_ or p_ so you don't get them mixed up with column names (which is totally possible).

answered Mar 31, 2014 at 1:04

Burg

2,0583 gold badges20 silver badges23 bronze badges

1 Comment

Senju Over a year ago

Thats not it. I think it has something to do with the IF statements. I took them out and it created the function. With the IF statements, however, it does not.

Collectives™ on Stack Overflow

Creating a function. Error: syntax error at or near ";" OR Error: No language specified

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related