Postgresql create function error

Question

What am I doing wrong in the following function?

CREATE OR REPLACE FUNCTION extended_sales(area_type varchar, area_code varchar, dpci varchar) RETURNS TABLE(task_id bigint, location_id int)as
$BODY$
BEGIN 
 IF area_type = 1 THEN
 RETURN QUERY select T.task_id, T.location_id from store_price.task T where T.task_payload->>'str_area_type_i'='3';
 ELSE IF area_type = 2 THEN
 RETURN QUERY select T.task_id, T.location_id from store_price.task T where T.due_date < '2017-10-06';
 ELSE
 RETURN QUERY select T.task_id, T.location_id from store_price.task T where task_payload->>'str_area_type_i'='1' and task_payload->>'str_area_c'='7' and due_date < '2016-11-07';
 END IF;
END
$BODY$ language plpgsql;

When I run the above function it gives the following error

ERROR: syntax error at end of input
LINE 17: $BODY$ language plpgsql;

Any help is appreciated. Thanks.

• Hambone: I didn't try to compile it, but don't you need a semicolon after END?
• Vao Tsun: do you need return; before end?
• Erwin Brandstetter: @Hambone: No, you don't. That's optional at the end.

Answer 1

You have two IF statements and one END IF. Use ELSIF if you want to have a single IF statement.

 IF area_type = 1 THEN
 RETURN QUERY select T.task_id, T.location_id from store_price.task T where T.task_payload->>'str_area_type_i'='3';
 ELSIF area_type = 2 THEN
 RETURN QUERY select T.task_id, T.location_id from store_price.task T where T.due_date < '2017-10-06';
 ELSE
 RETURN QUERY select T.task_id, T.location_id from store_price.task T where task_payload->>'str_area_type_i'='1' and task_payload->>'str_area_c'='7' and due_date < '2016-11-07';
 END IF;