Subquery for column names of tablefunc crosstab queries

Is it possible to get the values for the names of the columns for the crosstab query from a subquery?

CREATE TABLE mytable(employee_name text, month date, allocation double precision);
INSERT INTO mytable VALUES('A','2017-11-30','0.5');
INSERT INTO mytable VALUES('B','2017-11-30','0.8');
INSERT INTO mytable VALUES('B','2017-12-31','0.5');

SELECT * FROM crosstab
(
  'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
  'SELECT DISTINCT month FROM mytable ORDER BY 1'
)
AS
(
    employee_name text  
    # Here a subquery to get column names
    # automatically eg : 'SELECT DISTINCT month FROM mytable'
);

No, you can't make your resultset's schema dependent on the values inside the table. But you can use dynamic sql to generate a query.

SELECT FORMAT($

  SELECT * FROM crosstab
  (
    'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
    'SELECT DISTINCT month FROM mytable ORDER BY 1'
  )
  AS
  (
    employee_name text,
    %s
  )
$,
  'something here...'
);

Now you just want to fill out that 'something here..'

SELECT FORMAT(
    $
        SELECT * FROM crosstab
        (
            'SELECT employee_name , month , allocation
             FROM mytable
            ORDER BY 1, 2',
            'SELECT DISTINCT month FROM mytable ORDER BY 1'
        )
        AS
        (
            employee_name text,
            %s
        )
    $,
    string_agg(
        FORMAT('%I %s', month, 'double precision'), ', '
        ORDER BY month
    )
)
FROM (
    SELECT DISTINCT month
    FROM mytable
)
    AS t;

Then run the query that produces, and you get..

employee_name | 2017-11-30 | 2017-12-31 
---------------+------------+------------
 A             |        0.5 |           
 B             |        0.8 |        0.5
(2 rows)

StackExchange Database Administrators Q#192494, answer score: 3