function SETOF return type with variable columns?

Hello

2008/8/20 James Neff <james.neff@tethyshealth.com>:

Greetings,

Is it possible to have a function with a return type of SETOF that has
variable number of return columns?

No. Number and result types have to be known in parse time. Use array
without it.

The input parameter for this function will be a String containing a number
of codes separated by a tilde character. I would like to have 1 output
column for each of these codes, but the number of input codes may change for
each time the function is called.

For example:

Input: ABC1~XYZ2~MNO3

simply string_to_array('ABC1~XYZ2~MNO3', '~');

Output result set will then look like this where name, ABC1, XYZ2, and MNO3
are column headers and not a data row:

name | ABC1 | XYZ2 | MNO3
bob | 9 | 3 | 1
john | 5 | 2 | 1
...

Every row in the output set will contain a name and then a count of the
number of codes matched for that name. But the codes queried for would
change with each call of the function.
Does it make sense what I am asking for?

it has sense, but postgresql doesn't support this feature
Regards
Pavel Stehule

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?

The input parameter for this function will be a String containing a number
of codes separated by a tilde character. I would like to have 1 output
column for each of these codes, but the number of input codes may change for
each time the function is called.

For example:

Input: ABC1~XYZ2~MNO3

Output result set will then look like this where name, ABC1, XYZ2, and MNO3
are column headers and not a data row:

name | ABC1 | XYZ2 | MNO3
bob | 9 | 3 | 1
john | 5 | 2 | 1
...

Every row in the output set will contain a name and then a count of the
number of codes matched for that name. But the codes queried for would
change with each call of the function.
Does it make sense what I am asking for?

PostgreSQL functions are for the most part strictly bound to their
return type. If you are willing to coerce everything to text, you
might be able to return 'setof text[]' instead of a record. This may
require more acrobatics inside the function than you really want to
get in to (especially if you are getting into deep dynamic sql,
iterating the column lists in information_schema and building
queries).

Another possibility is to make a custom type that has at least as many
columns as you are likely to use, and make them all text...set the
ones you want and leave the rest null. This is, uh, fairly lame but
I'm trying to think outside the box here :-).

merlin

"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.

regards, tom lane

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;

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