Concatenate return type for a crosstab() query from another query

Question

I can't post my original query here, it's too complex. So I am explaining my problem with a sample query:

Working pivot query:

Select pivot.* from public.crosstab($$ select 'na' as na, 1 as analysis,100 as value 
 union all select 'ba' as na, 2 as analysis,100 as value $$,
 $$ VALUES ('1'), ('2')$$
) as pivot (na text, "1" integer,"2" integer)

But I would like to use it like this:

Select pivot.* from public.crosstab($$ select 'na' as na, 1 as analysis,100 as value 
 union all select 'ba' as na, 2 as analysis,100 as value $$,
 $$ VALUES ('1'), ('2')$$
) as pivot select '(na text,"1" integer,"2" integer)'

I want to add the return type from a query (bold emphasis). How to do that?

Answer 1

Clear test case

First I formated and clarified your example:

SELECT * FROM public.crosstab(
   $$VALUES ('na', 1, 100)   -- VALUES expression does the same, shorter
          , ('ba', 2, 300)$$ -- no column names needed (ignored anyway)
  ,$$VALUES ('1'), ('2')$$
   ) AS pivot (na text, co11 int, col2 int)  -- sane column names

Input table:

col1  key  val
---------------
na    1    100
ba    2    300

Output table

na    co11    col2
--------------------
na    100     <NULL>
ba    <NULL>  300

You can pick output column names freely, no need to stick to unwieldy names like "1", "2". Only the data types have to match.

Dynamic return type

You cannot provide the column definition list from a query. We have had many similar requests here on SO. They all clash with the nature of SQL, which does not accept a return type "after the fact". Must be declared with the query, at least at the time of executing it.

You would have to build the crosstab() query with the output of the SELECT query you want to add in your 2nd example. Two round trips to the server.

But we can read a well know type from the system catalogs - that's what SELECT * FROM tbl does after all. I have tried hard and implemented what I found in this related answer:

• Dynamic alternative to pivot with CASE and GROUP BY

My function crosstab_n() is probably your best shot. Read the linked answer!

It's a wrapper around crosstab() that takes the query string (just like crosstab()) and an additional polymorphic parameter that provides the return type. We still cannot pass the return type on the fly. The only acceptable way for SQL is to read it from the system catalog. So we install a composite type "on the fly" by way of creating a temporary table:

CREATE TEMP TABLE my_pivot (na text, col1 int, col2 int);

SELECT * FROM crosstab_n(
    $$VALUES ('na', 1, 100), ('ba', 2, 100)$$
    ,NULL::my_pivot
    );

Result as above. Voilà!

If you want the temp table to last for the transaction only, add ON COMMIT DROP to it. Both statements must be executed in one transaction then. Details:

• Select from a table variable