Re: Function returning SETOF returns nothing

On Thu, Apr 5, 2012 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Coby Beck <coby101(at)gmail(dot)com> writes:
>> Thanks for the advice, Tom...I am still having trouble as many
>> variations tried all give compile errors.
>
> More modern versions give a more useful error message:
>
> ERROR:  RETURN NEXT must specify a record or row variable in function returning row
> LINE 4:     RETURN NEXT (SELECT ''old'' as type, ''item'' as item, '...
>                        ^
>
> So what you need to do is declare a variable of type ForecastData,
> assign into the fields of that variable, and then "RETURN NEXT varname".
>
>                        regards, tom lane

Thank you Tom, I am up and running now. For the archives, the
following code is performing as expected:

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

CREATE OR REPLACE FUNCTION CreateDefaultForecasts(INTEGER) RETURNS
SETOF ForecastData AS '
DECLARE Answers ForecastData;
BEGIN
SELECT ''old'' as type, ''item'' as item, ''descr'' as descr,
''unit'' as unit, $1 as qty, 0 as rate, 0 as amt into answers;
RETURN NEXT Answers;
SELECT ''old'' as type, ''item'' as item, ''descr'' as descr,
''unit'' as unit, $1 + 1 as qty, 0 as rate, 0 as amt into answers;
RETURN NEXT Answers;
RETURN;
END;
' LANGUAGE 'plpgsql';

Cheers,

Coby