Re: Function returning SETOF returns nothing

On Wed, Apr 4, 2012 at 11:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Coby Beck <coby101(at)gmail(dot)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