That is one of most wanted features of PostgreSQL, what is not solved yet,,,
But it seems will be soon with introductions of Stored Procedures...
For now, you must "know" what result (columns) you expects...
So the only one option for now is to use Dynamic SQL - to build your query dynamically based on data in your table (based on column what should be pivoted)..
And then execute that query...
You can use your client side language to build SQL or inisde DB you could make function what returns "text" as your Dynamic SQL and then execute it from your client...
Kind Regards,
Misa