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

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).

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related