Re: [GENERAL] function SETOF return type with variable columns?

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;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general