PostgreSQL: control reached end of function without RETURN

Question

I'm trying to create a function which returns either X rows or 0 rows.

This is my code:

    ...
    IF var != '' THEN
        RETURN var;
    ELSE
        RETURN NULL;
    END IF;
END;
$$
LANGUAGE 'plpgsql';

If I do:

    ...
    IF var != '' THEN
        RETURN var;
    ELSE
        RETURN;
    END IF;
END;
$$
LANGUAGE 'plpgsql';

I get the error:

ERROR:  missing expression at or near ";"
LINE 19: RETURN;

I'm unable to, as StackOVerflow denies the update. Says there's more code than text. — Michael Nielsen
– Michael Nielsen, Commented Jan 20, 2017 at 13:11
did you declare output variables?.. I mean you cant return without anything if you did not — Vao Tsun
– Vao Tsun, Commented Jan 20, 2017 at 13:12

Laurenz Albe · Accepted Answer · 2017-01-20 13:38:48Z

4

A normal function like the one you defined (RETURNS text) will always return exactly one value.

So unless the function is defined as RETURNS void, you'll have to return a value, i.e. you cannot use RETURN; without an argument.

If you want to return a different number of rows (0 or a number greater than 1), you need a set returning function.

Such a function is declared as RETURNS SETOF text (or some other type), and from inside the function you use RETURN NEXT val; to add a row to the function output and RETURN; without argument to terminate execution.

Such a function can be used like a table in queries.

answered Jan 20, 2017 at 13:38

Laurenz Albe

256k21 gold badges307 silver badges382 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: control reached end of function without RETURN

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