Thread: function SETOF return type with variable columns?
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? Thanks in advance, James
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
>
> Thanks in advance,
> James
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
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:
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
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