Re: [patch] Proposal for \rotate in psql - Mailing list pgsql-hackers

    Pavel Stehule wrote:

> [ \rotate being a wrong name ]

Here's an updated patch.

First it renames the command to \crosstabview, which hopefully may
be more consensual, at least it's semantically much closer to crosstab .

> The important question is sorting output. The vertical header is
> sorted by first appearance in result. The horizontal header is
> sorted in ascending or descending order. This is unfriendly for
> often use case - month names. This can be solved by third parameter
> - sort function.

I've thought that sorting with an external function would be too
complicated for this command, but sorting ascending by default
was not the right choice either.
So I've changed to sorting by first appearance in result (like the vertical
header), and sorting ascending or descending only when specified
(with +colH or -colH syntax).

So the synopsis becomes: \crosstabview [ colV [+ | -]colH ]

Example with a time series (daily mean temperatures in Paris,2014),
month names across, day numbers down :

select
  to_char(w_date,'DD') as day ,
  to_char(w_date,'Mon') as month,
  w_temp from weather
  where w_date between '2014-01-01' and '2014-12-31'
  order by w_date
\crosstabview

 day | Jan | Feb | Mar | Apr | May | Jun | ...[cut]
-----+-----+-----+-----+-----+-----+-----+-
 01  |     8 |   8 |   6 |  16 |    12 |  15 |
 02  |    10 |   6 |   6 |  15 |    12 |  16 |
 03  |    11 |   5 |   7 |  14 |    11 |  17 |
 04  |    10 |   6 |   8 |  12 |    12 |  14 |
 05  |     6 |   7 |   8 |  14 |    16 |  14 |
 06  |    10 |   9 |   9 |  16 |    17 |  20 |
 07  |    11 |  10 |  10 |  18 |    14 |  24 |
 08  |    11 |   8 |  12 |  10 |    13 |  22 |
 09  |    10 |   6 |  14 |  12 |    16 |  22 |
 10  |     6 |   7 |  14 |  14 |    14 |  19 |
 11  |     7 |   6 |  12 |  14 |    12 |  21 |
...cut..
 28  |     4 |   7 |  10 |  12 |    14 |  16 |
 29  |     4 |     |  14 |  10 |    15 |  16 |
 30  |     5 |     |  14 |  14 |    17 |  18 |
 31  |     5 |     |  14 |     |    16 |     |

The month names come out in the expected order here,
contrary to what happened with the previous iteration of
the patch which forced a sort in all cases.
Here it plays out well because the single "ORDER BY w_date" is
simultaneously OK for the vertical and horizontal headers,
a common case for time series.

For more complicated cases, when the horizontal and vertical
headers should be ordered independantly, and
in addition the horizontal header should not be sorted
by its values, I've toyed with the idea of sorting by another
column which would supposedly be added in the query
just for sorting, but it loses much in simplicity. For the more
complex stuff, users can always turn to the server-side methods
if needed.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite