Re: function SETOF return type with variable columns?

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?
>
> 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