Postgres Crosstab Dynamic Number of Columns

Question

In Postgres 9.4, I have a table like this:

id  extra_col  days value
--  ---------  ---  -----
1   rev          0      4
1   rev         30      5
2   cost        60      6

i want this pivoted result

id  extra_col   0  30  60
--  ---------  --  --  --
1   rev         4   5   
2   cost                6

this is simple enough with a crosstab. but i want the following specifications:

• day column will be dynamic. sometimes increments of 1,2,3 (days), 0,30,60 days (accounting months), and sometimes in 360, 720 (accounting years).
• range of days will be dynamic. (e.g., 0..500 days versus 1..10 days).
• the first two columns are static (id and extra_col)
• The return type for all the dynamic columns will remain the same type (in this example, integer)

Here are the solutions I've explored, none of which work for me for the following reasons:

1. Automatically creating pivot table column names in PostgreSQL - requires two trips to the database.
2. Using crosstab_hash - is not dynamic

From all the solutions I've explored, it seems the only one that allows this to occur in one trip to the database requires that the same query be run three times. Is there a way to store the query as a CTE within the crosstab function?

SELECT *
FROM
CROSSTAB(
    --QUERY--,
    $$--RUN QUERY AGAIN TO GET NUMBER OF COLUMNS--$$
)
as ct (
    --RUN QUERY AGAIN AND CREATE STRING OF COLUMNS WITH TYPE--
)

Answer 1

Every solution based on any buildin functionality needs to know a number of output columns. The PostgreSQL planner needs it. There is workaround based on cursors - it is only one way, how to get really dynamic result from Postgres.

The example is relative long and unreadable (the SQL really doesn't support crosstabulation), so I will not to rewrite code from blog here http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html.