Here's an updated patch improving on how the horizontal and vertical headers can be sorted.
The discussion upthread went into how it was desirable to have independant sorts for these headers, possibly driven by another column, in addition to the query's ORDER BY.
The optional scolV/scolH columns drive sorts for respectively colV/colH (colV:scolV somehow means SELECT colV from... order by scolV)
colG1,... in 3rd arg indicate the columns whose contents form the grid cells, the typical use case being that there's only one such column. By default it's all columns minus colV and colH.
For example,
SELECT cust_id, cust_name, cust_date, date_part('month, sales_date), to_char(sales_date, 'Mon') as month, amount FROM sales_view WHERE [predicates] [ORDER BY ...]
If we want to look at <amount> in a grid with months names across, sorted by month number, and customer name in the vertical header, sorted by date of acquisition, we could do this:
\crosstabview +cust_name:cust_date +5:4 amount
or letting the vertical header being sorted by the query's ORDER BY, and the horizontal header same as above:
\crosstabview cust_name +5:4 amount
or sorting vertically by name, if it happens that the ORDER BY is missing or is on something else:
\crosstabview +cust_name +5:4 amount
I am playing with this patch, and I have following comments:
1. maybe we can decrease name to shorter "crossview" ?? I am happy with crosstabview too, just crossview is correct too, and shorter
2. Columns used for ordering should not be displayed by default. I can live with current behave, but hiding ordering columns is much more practical for me
3. This code is longer, so some regress tests are recommended - attached simple test case
Regards
Pavel
Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
