Postgresql syntax error for if statement in a function

Question

I am trying to write a function in postgresql and i am getting a syntax error for the if statement:

CREATE OR REPLACE FUNCTION getallcampuses(IN a character varying, IN b character varying)
  RETURNS TABLE(campusid charactervarying, campusname character varying) AS
$BODY$
BEGIN
if $1 = 'PREK' then
     SELECT * from "SIS_campus";
ELSE
    BEGIN
       IF $2 = 'DAll' then
           SELECT distinct(district_id) || 'ALL' AS CampusID, ' District' AS CampusName

           UNION

           SELECT campus_id, name
           FROM   "SIS_campus"
           WHERE  district_name IS NOT NULL
           order by name;
       Elsif $2 = 'All' then

           SELECT campus_id, name
           FROM   "SIS_campus"
           WHERE  district_name IS NOT NULL
           and isnumeric(name) = 0
           order by name;
        end if;
    END
end if;
END
$BODY$
LANGUAGE sql;

Here is the error:

ERROR:  syntax error at or near "if"
LINE 5: if $1 = 'PREK' then
        ^

Thanks for including a complete function body and the exact error message text. That's so rare, and is appreciated. (In future, please put your PostgreSQL version in the text too, though). — Craig Ringer
– Craig Ringer, Commented Sep 12, 2014 at 16:52

Community · Accepted Answer · 2017-05-23 12:03:55Z

2

The function body is PL/PgSQL, but you declared it LANGUAGE sql.

Use LANGUAGE plpgsql if you're writing PL/PgSQL.

(related prior answer)

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Sep 12, 2014 at 16:47

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Postgresql syntax error for if statement in a function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related