On Wed, Aug 20, 2008 at 12:59 PM, James Neff
<james.neff@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx> wrote:
> "Merlin Moncure" <mmoncure@xxxxxxxxx> 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;
