Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function returning SETOF returns nothing

Function returning SETOF returns nothing

From
Coby Beck
Date:
Hi all,

I have a monster function full of IF ELSIF branches that is supposed
to return a SETOF value, though regardless of its route through the
logic it only returns an empty row.

Fortunately, reduced to almost nothing I have the sme problem so it
will likely be a simply matter.  Function and table definition follow:

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 '
    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';


dbtestvanek=# select * from CreateDefaultForecasts(314028);
 type | item | descr | unit | qty | rate | amt
------+------+-------+------+-----+------+-----
(0 rows)

The actual function inserts into ForecastData and then returns (SELECT
* from ForecastData where type = ''new''); or rather should do, but no
matter what it returns the same 0 rows.

What am I doing wrong, and, bonus question, where should I have found
the answer in the docs?

Thanks for your time and attention!

Coby

psql 7.4.27 on a Linux server.

Re: Function returning SETOF returns nothing

From
Tom Lane
Date:
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

Re: Function returning SETOF returns nothing

From
Coby Beck
Date:
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

Re: Function returning SETOF returns nothing

From
Tom Lane
Date:
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

Re: Function returning SETOF returns nothing

From
Coby Beck
Date:
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