Thread: Please help!

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?

I tried posting these questions to the general mailing list, but didn't
receive any feedback.  Any help would be greatly appreciated.

Thanks,

Brandon

Brandon E Hofmann <Brandon_E_Hofmann@notes.ntrs.com> writes:
> In plpgsql, how do you return back a result set that is determined and
> generated at runtime based on a report request?

If I understand what you are asking for, you don't.

> Also why does plpgsql require you to define what is returned?

plpgsql is not imposing this, the system as a whole does.  Else, the
parser would have no idea what to expand "*" to in
select * from myfunc(42);

There are facilities that let you use the same textual function "myfunc"
for different result column sets, but this doesn't get you off the hook
of having to tell the calling query what the column set is going to be.
        regards, tom lane