First it renames the command to \crosstabview, which hopefully may be more consensual, at least it's semantically much closer to crosstab .
thank you very much :)
> 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
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.
it is looking well
I'll do review tomorrow
Regards
Pavel
Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
