Re: Function returning SETOF returns nothing

On Wed, Apr 4, 2012 at 11:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

Thanks for the advice, Tom...I am still having trouble as many
variations tried all give compile errors.  This is what I thought
should work based on your mail and the "37.7.1.2. RETURN NEXT" section
of this page http://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html
:

CREATE TABLE ForecastData(type TEXT, item TEXT, descr TEXT, unit TEXT,
qty FLOAT, rate FLOAT, amt FLOAT);

CREATE OR REPLACE FUNCTION CreateDefaultForecasts() RETURNS SETOF
ForecastData AS '
   BEGIN
   RETURN NEXT (SELECT ''old'' as type, ''item'' as item, ''descr'' as
descr, ''unit'' as unit, 0 as qty, 0 rate, 0 as amt);
   RETURN;
   END;
' LANGUAGE 'plpgsql';

but results are:

dbtestvanek=# select * from CreateDefaultForecasts();
ERROR:  incorrect argument to RETURN NEXT at or near "("
CONTEXT:  compile of PL/pgSQL function "createdefaultforecasts" near line 2

QUERY, no brackets, other thrashing around brings similar results.

The above stub function aside, not being able to pass in a parameter
is going to be a problem.  What would a clever person do if they want
some complexity based on one or more parameters to determine a set of
rows for return?  I am not sure what limits I will run into if I use
LANGUAGE 'sql', I know I can pass a parameter(s) but the only 'sql'
functions in this application are very simple.

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

7.4 is hungry...point taken.  I knew I would be scolded!

Cheers,

Coby