Postgres Crosstab query with CTE (with clause)

Question

Recently started working on Postgres and need to pivot data.
I wrote the following query:

select * 
from crosstab (
$$  
    with tmp_kv as (
        select distinct pat_id  
            ,col.name as key, replace(replace(replace(value, '[',''), ']', ''),'"','') as value
        from (
            select p.Id as pat_id, nullif(kv.key,'undefined')::int as key, trim(kv.value::text,'"') as value
            from pat_table p 
            left join e_table e on e.pat_id = p.id and e.id is null
            ,jsonb_each_text(p.data) as kv
        ) t 
        left join lateral (
            select name::text as name from public.config_fields fld 
            where id = t.key 
        ) col on true
    )
    select pat_id, key, value 
    from tmp_kv 
    where nullif(trim(key),'') is not null
    order by pat_id, key 
$$,$$
    select distinct key from tmp_kv -- (Get error "relation "tmp_kv" does not exist" )
    where nullif(trim(key),'') is not null
    order by 1  
$$
) as (
    pat_id bigint
    ...
    ...
);

Query works if I take the WITH clause out into temporary table. But will be deploying it to production with read replicas, so need it to be working with a CTE. Is there a way?

Answer 1

The two queries passed as strings to the crosstab() function are separate queries.
A CTE can only be attached to a single query.
What you ask for is strictly impossible.

Since you have to spell out the (static) return type for crosstab() anyway, and the result of the query in the 2nd parameter has to match that, it's pointless to use a query with a dynamic result as 2nd parameter to begin with.