Unable to use Common Table Expressions in Postgres Crosstab Query
<p>I'm trying to use perform a pivot operation on some data using Postgres' <code>tablefunc</code> extension's <code>CROSSTAB</code> function. The data needs to undergo some transformations first, which I perform in some <em>common table expressions</em>.</p>

<p>However, it appears <code>CROSSTAB</code> can't see the results of those expressions.</p>

<p>For example, this query sourcing data from a <em>temporary table</em> works fine:</p>

<pre><code>CREATE TEMPORARY TABLE
  temporary_table
    (name, category, category_value)
ON COMMIT DROP
AS (
  VALUES
    ('A',  'foo',    1             ),
    ('A',  'bar',    2             ),
    ('B',  'foo',    3             ),
    ('B',  'bar',    4             )
);

SELECT * FROM
  CROSSTAB(
    'SELECT * FROM temporary_table',
    $$
      VALUES
        ('foo'),
        ('bar')
    $$
  ) AS (
    name TEXT,
    foo  INT,
    bar  INT
  );
</code></pre>

<p>and, as expected, produces the following output:</p>

<pre><code>name | foo     | bar
text | integer | integer
---- | ------- | -------
A    |       1 |       2
B    |       3 |       4
</code></pre>

<p>But the same query, this time using <em>common table expressions</em> doesn't run:</p>

<pre><code>WITH
  common_table
    (name, category, category_value)
AS (
  VALUES
    ('A',  'foo',    1             ),
    ('A',  'bar',    2             ),
    ('B',  'foo',    3             ),
    ('B',  'bar',    4             )
)
SELECT * FROM
  CROSSTAB(
    'SELECT * FROM common_table',
    $$
      VALUES
        ('foo'),
        ('bar')
    $$
  ) AS (
    name TEXT,
    foo  INT,
    bar  INT
  )
</code></pre>

<p>and produces the following error:</p>

<pre><code>ERROR:  relation "common_table" does not exist
LINE 1: SELECT * FROM common_table
                      ^
QUERY:  SELECT * FROM common_table

********** Error **********

ERROR: relation "common_table" does not exist
SQL state: 42P01
</code></pre>

<p>I take it this means the text query (<code>SELECT * FROM common_table</code>) runs in some sort of different context?</p>

<hr>

<p><strong>Note:</strong> The <code>tablefunc</code> extension must be enabled for <code>CROSSTAB</code> to be available:</p>

<pre><code>CREATE EXTENSION IF NOT EXISTS tablefunc;
</code></pre>