He visto que hay algunas preguntas similares como esta, pero no he entendido cómo codificarlo yo mismo. Tenga en cuenta que solo soy un principiante en este campo.

Básicamente quiero pivotar la tabla así:

zoom |    day     | point         zoom | 2015-10-01 |  2015-10-02 | ......
------+-----------+-------  ---> ------+------------+-------------+
   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
   :
   :
   :

El tiempo puede variar

Resultados a la izquierda que obtengo con:

SELECT 
zoom,
to_char(date_trunc('day', time), 'YYYY-MM-DD') AS day,
count(*) as point
FROM province
WHERE time >= '2015-05-01' AND time < '2015-06-01'
GROUP BY to_char(date_trunc('day', time), 'YYYY-MM-DD'), zoom;

He leído que hay algunos problemas si usocount y también que sería mejor si usoCASE yGROUP BY, sin embargo, no tengo idea de cómoCASE esta.

Crosstab en sí mismo no admite la creación dinámica de nombres de columnas, pero eso se puede lograr concrosstab_hash, si lo entendí correctamente.

Probablemente esta sea una buena solución:http://okbob.blogspot.ca/2008/08/using-cursors-for-generating-cross.html Sin embargo, estoy atascado con él tratando de programarlo yo mismo.

Tengo que usar este tipo de pivote con bastante frecuencia, por lo que solicitaría cualquier tipo de ayuda y explicación adicional detrás de esto.

Editar1

Estoy tratando de descubrir cómo funciona la tabla de referencias cruzadas con las fechas, actualmente sin devolver nombres dinámicos de columnas. Más adelante explicaré por qué. Está relacionado con la pregunta principal. Para este ejemplo, estoy usando solo un período de 2 fechas.

Basado en la respuesta de @Erwin Brandstetter:

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);

Los resultados devueltos son:

zoom |    day1    |    day2     | 
-----+------------+-------------+
   1 |    201     |     685     |
   2 |     43     |     346     | 
   3 |     80     |     534     | 
   4 |    324     |     786     | 

Estoy tratando de conseguir esto

zoom | 2015-10-01 |  2015-10-02 | 
-----+------------+-------------+
   1 |    201     |     685     |
   2 |     43     |     346     | 
   3 |     80     |     534     | 
   4 |    324     |     786     | 

pero mi consulta no funciona:

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

Edit1, Q1. ¿Por qué esto no funciona y cómo puedo devolver resultados como ese?

He leído los enlaces que @Erwin Brandstetter me proporcionó, especialmente este:Ejecutar una consulta de tabla cruzada dinámica. He copiado / pegado su función:

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;

y llamarlo con consulta

SELECT pivottab('province','zoom','day','point','date');

La función me devolvió:

                         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)

Entonces, cuando edité la consulta y agregué; (sería bueno que ya esté allí) obtuve:

ERROR:  column "province.point" must appear in the GROUP BY clause or be used in an aggregate function

Edit1, Q2. ¿Alguna idea de cómo solos esto?

Edit1, Q3. Supongo que la siguiente pregunta será cómo ejecutar la función automáticamente, que también se menciona en el mismo enlace, pero quedó bloqueado en los pasos anteriores.