Re: function SETOF return type with variable columns?

Merlin Moncure wrote:
> On Wed, Aug 20, 2008 at 12:59 PM, James Neff
> <james(dot)neff(at)tethyshealth(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "Merlin Moncure" <mmoncure(at)gmail(dot)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