Hello,

I want to write a pl/pgsql function that returns one or more rows with
multiple (1..n) columns. The tricky thing about this is, that I don't
know how many columns the return type will have.

The reason for that is, that I have two tables:

table1
id type
-- ----
01 employee
02 student

table2
id refid name
-- ----- -----
01 01 John
02 01 Max

When I select from table1 for all employees, I want a result like
this:

id type name1 name2
-- ---- ----- -----
01 employee John Max

So my thoughts where to set up a function which creates the type in
dependence of the results from table2 and then to set up another
function with the created return type:

CREATE TYPE testtype as (id int, type character varying, name1
character varying, name1 character varying);

CREATE OR REPLACE FUNCTION test() RETURNS SETOF testtype as $$
....
$$ LANGUAGE plpgsql;

But when I try this, psql tells me that the type is unknown...
Any ideas?! Thank you in advance!

Marc

On jan. 25, 16:11, "marc.hani...@googlemail.com"
<marc.hani...@googlemail.com> wrote:
> Hello,
>
> I want to write a pl/pgsql function that returns one or more rows with
> multiple (1..n) columns. The tricky thing about this is, that I don't
> know how many columns the return type will have.
>
> The reason for that is, that I have two tables:
>
> table1
> id type
> -- ----
> 01 employee
> 02 student
>
> table2
> id refid name
> -- ----- -----
> 01 01 John
> 02 01 Max
>
> When I select from table1 for all employees, I want a result like
> this:
>
> id type name1 name2
> -- ---- ----- -----
> 01 employee John Max
>
> So my thoughts where to set up a function which creates the type in
> dependence of the results from table2 and then to set up another
> function with the created return type:
>
> CREATE TYPE testtype as (id int, type character varying, name1
> character varying, name1 character varying);
>
> CREATE OR REPLACE FUNCTION test() RETURNS SETOF testtype as $$
> ...
> $$ LANGUAGE plpgsql;
>
> But when I try this, psql tells me that the type is unknown...
> Any ideas?! Thank you in advance!
>
> Marc

I think what you are looking for is a crosstab.
If this is a generic problem than as far as I know there is no way you
could do this in Postgres (MSAccess could do it) - if you don't know
the resulting columns.
At least I have been looking for a crosstab solution in Postgres where
you didnt have to specify the resulting colums for a long-long time
and couldnt find it.

If you have a special case where you know the possible resulting
columns you could it by either CASE or using tablefunc crosstab - just
specify all possible output columns.

Hth.
SWK.