Merlin Moncure wrote:
On Wed, Aug 20, 2008 at 12:59 PM, James Neff
<james.neff@tethyshealth.com> wrote:
Greetings,
Is it possible to have a function with a return type of SETOF that has
variable number of return columns?
On Wed, Aug 20, 2008 at 10:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Merlin Moncure" <mmoncure@gmail.com> writes:
PostgreSQL functions are for the most part strictly bound to their
return type.
There is, however, the trick of declaring the function as "returns
record" and then specifying the names and types of the output columns
in the calling query. I'm not sure how practical that is to use with
a plpgsql function, and in any case it's not the syntax the OP asked
for; but it seems worth mentioning in this thread.
Here's another approach, using a refcursor: This is cheating
according to the rules set by the OP, but it's a great way to provide
a flexible way to return data from the database via a single function.
create or replace function doit() returs refcursor as
$$ declare r refcursor value 'result'; begin /* some query that puts data in refcursor */ end;
$$ language plpgsql;
-- from psql/app
begin;
select doit();
fetch all from result;
commit;
Thanks everyone for the input. I actually decided to build an XML fragment inside my stored proc and return it as a single text field. The calling process then uses XSL to transform the XML to the html table the user needs.
Hopefully your responses will help someone else who might have the same question in the future.
--Jim
