Re: function to return rows as columns?

[Thomas Kellerer <spam_eater(at)gmx(dot)net>]

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