Re: function to return rows as columns?

Linos, 27.02.2009 11:41:
> Hello,
>     i have a query that returns a result set like this:
>
> item | size | stock
> 123  | XL   | 10
> 123  | XXL | 5
> 123  | XS   | 3
>
> and i would like get the results like this:
>
> item | XL | XXL | XS
> 123  | 10  | 5     | 3
>
> i have been thinking how to do it with a plpgsql function but the number
> of sizes depend on the item that it is queried so i can not create a
> type and return it, i could create it like a text concatenating the
> stock and size of every row and returning the complete line text but i
> would have to process it in the application anyway so i am searching a
> solution that lets me return it like a record.
>
> I have been searching the list and maybe i could create the record type
> inside the function and to get the correct names and number of columns
> in the application side launching a query to get the number of sizes
> before call the function to specify the columns in the function call but
> maybe i am missing anything important here? any better (or more correct)
> way to do this? Thanks.

Check out the "crosstab" function in the "Tablefunc" module:

http://www.postgresql.org/docs/current/static/tablefunc.html