Postgresql WITH clause in crosstab queries

Question

I create a large query as a Common Table Expression - month_index using WITH clause. Is it possible to refer this Common Table Expression in a crosstab query's source sql?

When I did gets an error relation "month_index" does not exist

WITH month_index AS
(
      SELECT ...
)
SELECT * FROM CROSSTAB(
   'SELECT rowid AS row_name,
   CONCAT(''m'',monthno) AS category,
   nic5dindex AS value
   FROM month_index',
   '*<categorysql>*')
AS ct(..)

I use Postgresql 9.3.

Answer 1

You want to have your CTE fully contained within the crosstab. More like this... you can have a CTE in a crosstab, but not vice-versa.

    SELECT * 
FROM CROSSTAB(
$$
    WITH month_index AS
    (
       SELECT rowid AS row_name,
       CONCAT(''m'',monthno) AS category,
       nic5dindex AS value
       FROM source_data_table
    )
    SELECT * FROM month_index 
,
   '*<categorysql>*'
$$   
   )
AS ct(..)