If you're trying to do what I think you're trying to do, you might need to create the temp table before you create the function (in the same session). Posting a test case people could look at somewhere would be easier than reading a bunch of description about the problem. On Tue, Jun 13, 2006 at 12:59:33PM -0500, Brandon E Hofmann wrote: > > When using a temp table in plpgsql functions that has columns comprised > from many tables populated by joins, how do you specify a temp table return > type when its generated by select into and dropped dynamically? I get an > error when I specify returns setof temp_table. Also when I specify a > permanent table as the return type, I get extra blank columns in the result > set which are the additional columns of the permanent table I don't need > and the data is displayed in the wrong columns. > > I tried defining composite types, but get a runtime error that it isn't > available unless I first define it outside of the function. Since requests > are dynamic, its impossible for me to determine all possible composite > types required ahead of time. > > In plpgsql, how do you return back a result set that is determined and > generated at runtime based on a report request? Also why does plpgsql > require you to define what is returned? > > I'm relatively new to PostgreSQL and really impressed by its capabilities. > Do you have any examples showing how to define and return a dynamic result > set at runtime within a function? > > Any help would be greatly appreciated. > > Thanks, > > Brandon > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
