title: Dynamically generate columns in PostgreSQL
body: <p>I have seen that there are quit a few similar questions like this one, but I havent understood how to code it myself. Please have in mind that I am just a beginner in this field.</p>
  
  <p>Basically I want to pivot the table like this:</p>
  
  <pre><code>zoom |    day     | point         zoom | 2015-10-01 |  2015-10-02 | ......
  ------+-----------+-------  ---&gt; ------+------------+-------------+
     1 | 2015-10-01 |   201            1 |    201     |     685     |
     2 | 2015-10-01 |    43            2 |     43     |     346     | 
     3 | 2015-10-01 |    80            3 |     80     |     534     | 
     4 | 2015-10-01 |   324            4 |    324     |     786     | 
     5 | 2015-10-01 |    25            5 |     25     |     685     |
     1 | 2015-10-02 |   685 
     2 | 2015-10-02 |   346 
     3 | 2015-10-02 |   534 
     4 | 2015-10-02 |   555 
     5 | 2015-10-02 |   786
     :
     :
     :
  </code></pre>
  
  <p>Time can vary.</p>
  
  <p>Results on left I get with:</p>
  
  <pre><code>SELECT 
  zoom,
  to_char(date_trunc('day', time), 'YYYY-MM-DD') AS day,
  count(*) as point
  FROM province
  WHERE time &gt;= '2015-05-01' AND time &lt; '2015-06-01'
  GROUP BY to_char(date_trunc('day', time), 'YYYY-MM-DD'), zoom;
  </code></pre>
  
  <p>I have read that there are some issues if I use <code>count</code> and also that it would be better if I use <code>CASE</code> and <code>GROUP BY</code>, however I have no idea how to <code>CASE</code> this.</p>
  
  <p><code>Crosstab</code> itself doesnt support dynamic creation of column names, but that can be achieved with <code>crosstab_hash</code>, if I understood it correctly.</p>
  
  <p>This might be probably nice solution: <a href="http://okbob.blogspot.ca/2008/08/using-cursors-for-generating-cross.html" rel="noreferrer">http://okbob.blogspot.ca/2008/08/using-cursors-for-generating-cross.html</a> however I am stucked with it trying to program it myself.</p>
  
  <p>I have to use this kind of pivoting quite often, so I would appriciate any  kind of help and additional explanation behind it.</p>
  
  <p><strong>Edit1</strong></p>
  
  <p>I am trying to figure out how crosstab works with dates, currently without returning dynamic names of columns. Later on I will explain why. It is realted to the main question. For this example I am using only period of 2 dates.</p>
  
  <p>Based on @Erwin Brandstetter answer:</p>
  
  <pre><code>SELECT * FROM crosstab(
         'SELECT zoom, day, point
          FROM   province
          ORDER  BY 1, 2'
        , $$VALUES ('2015-10-01'::date), ('2015-10-02')$$)
  AS ct (zoom text, day1 int, day2 int);
  </code></pre>
  
  <p>returned results are:</p>
  
  <pre><code>zoom |    day1    |    day2     | 
  -----+------------+-------------+
     1 |    201     |     685     |
     2 |     43     |     346     | 
     3 |     80     |     534     | 
     4 |    324     |     786     | 
  </code></pre>
  
  <p>I am trying to get this</p>
  
  <pre><code>zoom | 2015-10-01 |  2015-10-02 | 
  -----+------------+-------------+
     1 |    201     |     685     |
     2 |     43     |     346     | 
     3 |     80     |     534     | 
     4 |    324     |     786     | 
  </code></pre>
  
  <p>but my query doesnt work:</p>
  
  <pre><code>SELECT *
  FROM crosstab(
        'SELECT *
         FROM province
         ORDER  BY 1,2')
  AS ct (zoom text, "2015-10-01" date, "2015-10-02" date);
  
  ERROR:  return and sql tuple descriptions are incompatible
  </code></pre>
  
  <p><strong>Edit1, Q1. Why does this doesnt work and how can I return results like that?</strong></p>
  
  <p>I have read links that @Erwin Brandstetter provided me, especially this one: <a href="https://stackoverflow.com/questions/36804551/execute-a-dynamic-crosstab-query/36831103#36831103">Execute a dynamic crosstab query</a>. I have copied/pasted his function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION pivottab(_tbl regclass, 
                                      _row text, _cat text, 
                                      _expr text,
                                      _type regtype)  
  RETURNS text AS
  $func$
  DECLARE
     _cat_list text;
     _col_list text;
  BEGIN
  -- generate categories for xtab param and col definition list    
  EXECUTE format(
   $$SELECT string_agg(quote_literal(x.cat), '), (')
          , string_agg(quote_ident  (x.cat), %L)
     FROM  (SELECT DISTINCT %I AS cat FROM %s ORDER BY 1) x$$
   , ' ' || _type || ', ', _cat, _tbl)
  INTO  _cat_list, _col_list;
  
  -- generate query string
  RETURN format(
  'SELECT * FROM crosstab(
     $q$SELECT %I, %I, %s
        FROM   %I
        GROUP  BY 1, 2
        ORDER  BY 1, 2$q$
   , $c$VALUES (%5$s)$c$
     ) ct(%1$I text, %6$s %7$s)'
  , _row, _cat, _expr, _tbl, _cat_list, _col_list, _type
  );
  
  END
  $func$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>and call it with query</p>
  
  <pre><code>SELECT pivottab('province','zoom','day','point','date');
  </code></pre>
  
  <p>Function returned me:</p>
  
  <pre><code>                         pivottab                         
  ----------------------------------------------------------
   SELECT * FROM crosstab(                                 +
      $q$SELECT zoom, day, point                           +
         FROM   province                                   +
         GROUP  BY 1, 2                                    +
         ORDER  BY 1, 2$q$                                 +
    , $c$VALUES ('2015-10-01'), ('2015-10-02')$c$          +
      ) ct(zoom text, "2015-10-01" date, "2015-10-02" date)
  (1 row)
  </code></pre>
  
  <p>So when I edited the query and added ; (it would be nice that ; is already there) I got:</p>
  
  <pre><code>ERROR:  column "province.point" must appear in the GROUP BY clause or be used in an aggregate function
  </code></pre>
  
  <p><strong>Edit1, Q2. Any ideas how to solove this?</strong></p>
  
  <p><strong>Edit1, Q3. I guess next question will be how to execute function automaticlly</strong>, which is also mentioned on the same link, but got stucked on previous steps.</p>