plpgsql - Dynamically generate columns in PostgreSQL

Question

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.

Basically I want to pivot the table like this:

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
   :
   :
   :

Time can vary.

Results on left I get with:

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;

I have read that there are some issues if I use count and also that it would be better if I use CASE and GROUP BY, however I have no idea how to CASE this.

Crosstab itself doesnt support dynamic creation of column names, but that can be achieved with crosstab_hash, if I understood it correctly.

This might be probably nice solution: http://okbob.blogspot.ca/2008/08/using-cursors-for-generating-cross.html however I am stucked with it trying to program it myself.

I have to use this kind of pivoting quite often, so I would appriciate any kind of help and additional explanation behind it.

Edit1

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.

Based on @Erwin Brandstetter answer:

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

returned results are:

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

I am trying to get this

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

but my query doesnt work:

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. Why does this doesnt work and how can I return results like that?

I have read links that @Erwin Brandstetter provided me, especially this one: Execute a dynamic crosstab query. I have copied/pasted his function:

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;

and call it with query

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

Function returned me:

                         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)

So when I edited the query and added ; (it would be nice that ; is already there) I got:

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

Edit1, Q2. Any ideas how to solove this?

Edit1, Q3. I guess next question will be how to execute function automaticlly, which is also mentioned on the same link, but got stucked on previous steps.

Answer 1

The basic crosstab query for your example is simple:

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

But not with dynamic column names or a dynamic number of columns. As a compromise, you can have a fixed number of columns and only fill the leading ones. Basics:

• PostgreSQL Crosstab Query

Dynamic?

crosstab_hash is not going to help you with dynamic column names. It's for repeated use without typing a column definition list, but not for dynamic column names. Examples:

• Dynamically generate columns for crosstab in PostgreSQL
• Sql: Transposing rows into columns

For truly dynamic column names, you need two round trips to the server. Whether you retrieve the column names with a first query to build a second query, or you create a cursor or a temporary table or a prepared statement. Whatever you try, you need two round trips. SQL wants to know the return type at call time.

The closest I could get to a "dynamic" call is with my custom crosstab_n() function defined in this related answer:

• Dynamic alternative to pivot with CASE and GROUP BY

---

Or you give up the idea of a completely dynamic crosstab query (because, you know, it's impossible) and use a two-step workflow, like mentioned above.

1. Let a function generate the crosstab query text. You can use the function provided here (and adapt it to your needs!):

   • Execute a dynamic crosstab query

   _{In particular, remove GROUP BY 1, 2, since you do not aggregate rows before the cross tabulation.}

2. Execute the generated function.

---

For completeness, there is also the new crosstabview metacommand in psql in Postgres 9.6 (just released) - with similar functionality, and it can display dynamic column names (attaching dynamic names happens in the psql client, not in the Postgres server).