Re: plpgsql Result Sets

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461