On Thu, Apr 5, 2012 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Coby Beck <coby101@gmail.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