title: Dynamically generate columns for crosstab in PostgreSQL
body: <p>I am trying to create <code>crosstab</code> queries in PostgreSQL such that it automatically generates the <code>crosstab</code> columns instead of hardcoding it. I have written a function that dynamically generates the column list that I need for my <code>crosstab</code> query. The idea is to substitute the result of this function in the <code>crosstab</code> query using dynamic sql.</p>
  
  <p>I know how to do this easily in SQL Server, but my limited knowledge of PostgreSQL is hindering my progress here. I was thinking of storing the result of function that generates the dynamic list of columns into a variable and use that to dynamically build the sql query. It would be great if someone could guide me regarding the same.</p>
  
  <pre><code>
  -- Table which has be pivoted
  CREATE TABLE test_db
  (
      kernel_id int,
      key int,
      value int
  );
  
  INSERT INTO test_db VALUES
  (1,1,99),
  (1,2,78),
  (2,1,66),
  (3,1,44),
  (3,2,55),
  (3,3,89);
  
  
  -- This function dynamically returns the list of columns for crosstab
  CREATE FUNCTION test() RETURNS TEXT AS '
  DECLARE
      key_id int;
      text_op TEXT = '' kernel_id int, '';
  BEGIN
      FOR key_id IN SELECT DISTINCT key FROM test_db ORDER BY key LOOP
      text_op := text_op || key_id || '' int , '' ;
      END LOOP;
      text_op := text_op || '' DUMMY text'';
      RETURN text_op;
  END;
  ' LANGUAGE 'plpgsql';
  
  -- This query works. I just need to convert the static list
  -- of crosstab columns to be generated dynamically.
  SELECT * FROM
  crosstab
  (
      'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2',
      'SELECT DISTINCT key FROM test_db ORDER BY 1'
  )
  AS x (kernel_id int, key1 int, key2 int, key3 int); -- How can I replace ..
  -- .. this static list with a dynamically generated list of columns ?
  </code></pre>