Re: Function returning SETOF returns nothing - Mailing list pgsql-novice

Coby Beck <coby101@gmail.com> writes:
> CREATE OR REPLACE FUNCTION CreateDefaultForecasts(INTEGER) RETURNS
> SETOF ForecastData AS '
>     BEGIN
>     RETURN (SELECT ''old'' as type, ''item'' as item, ''descr'' as
> descr, ''unit'' as unit, 0 as qty, 0 rate, 0 as amt);
>     END;
> ' LANGUAGE 'plpgsql';

Um ... what Postgres version are you using?  Everything since about 8.0
will tell you pretty clearly what is wrong with this function:

ERROR:  RETURN cannot have a parameter in function returning set
LINE 4:     RETURN (SELECT ''old'' as type, ''item'' as item, ''desc...
                   ^
HINT:  Use RETURN NEXT or RETURN QUERY.

In a SETOF function, plain RETURN is just a flow-of-control command,
and you need to use RETURN NEXT (or possibly RETURN QUERY) to feed
actual rows back to the output.

If you really are using 7.x, you need to update.  Soon, before it
eats your data.

            regards, tom lane