Thread: SRFs returning records from a view
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
mark.lubratt@indeq.com wrote: > 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); select * from bluecard(1130)? Sincerely, Joshua D. Drake > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
> -------- Original Message -------- > Subject: Re: [GENERAL] SRFs returning records from a view > From: "Joshua D. Drake" <jd@commandprompt.com> > Date: Wed, June 01, 2005 4:39 pm > To: mark.lubratt@indeq.com > Cc: pgsql-general@postgresql.org > > mark.lubratt@indeq.com wrote: > > 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); > > select * from bluecard(1130)? > Sorry, 1130 is the job_id for the select's where clause in the function. Does that make sense? If I execute: select * from view_bluecard where job_id = 1130; I get the proper set of records. But, I want to add to them in the SRF as I explained above. Thus, I don't think I can do it with just a view (even using a union within the view as I don't know how many blanks the user willl want). Regards, Mark
AAARRRGGGHHH... Now I understand you, Josh. Sorry. You are correct. Thanks! Mark On Jun 1, 2005, at 4:39 PM, Joshua D. Drake wrote: > mark.lubratt@indeq.com wrote: >> 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); > > select * from bluecard(1130)? > > Sincerely, > > Joshua D. Drake > >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings > > > -- > Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 > PostgreSQL Replication, Consulting, Custom Programming, 24x7 support > Managed Services, Shared and Dedicated Hosting > Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ >