SRFs returning records from a view

From: mark(dot)lubratt(at)indeq(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: SRFs returning records from a view
Date: 2005-06-01 21:26:47
Message-ID: 20050601212647.30458.qmail@webmail12.prod.mesa1.secureserver.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

I have a view that I'm putting into a report in my application. I'd
like to get several blank lines returned by the view as well as the
legitimate data (to leave room in the report for manual entries). I
thought I could make a SRF that would return the data from the view and
then spit out a number of blank records.

As a first step in getting my SRF feet wet, I tried:

CREATE OR REPLACE FUNCTION bluecard(int4)
RETURNS SETOF view_bluecard AS
$BODY$declare
r view_bluecard%rowtype;
begin
for r in select * from view_bluecard where job_id = jn loop
return next r;
end loop;
return;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

where view_bluecard is the view that is already defined. When I try

select bluecard(1130);

I get the following error:

ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "bluecard" line 5 at return next

I've tried searching the archives with no luck. I've also been
following the SRF tutorial in the TechDocs section.

Can someone please shed some light on this for me? My plan is to get
this function going and then expand the SRF by creating some other rows
with the blanks as needed and "return next" those as well.

Thanks!
Mark

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2005-06-01 21:39:43 Re: SRFs returning records from a view
Previous Message Yudie Pg 2005-06-01 21:16:43 hpw to Count without group by