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.

I didn't try to compile it, but don't you need a semicolon after END? — Hambone
– Hambone, Commented Nov 13, 2017 at 12:14

klin · Accepted Answer · 2017-11-13 12:18:12Z

5

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;

answered Nov 13, 2017 at 12:18

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Postgresql create function error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related