Function returning SETOF returns nothing

From: Coby Beck <coby101(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Function returning SETOF returns nothing
Date: 2012-04-04 05:04:53
Message-ID: CAO_iwXP2w3C_3La84cbXi62Eto9O64c7XesJEruGr_hTfdri0w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2012-04-04 13:53:28 Re: Function returning SETOF returns nothing
Previous Message Jim Moon 2012-04-03 22:16:34 Re: How to connect to remote instance?