Re: [patch] Proposal for \crosstabview in psql

Hi

2016-01-22 19:53 GMT+01:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:

> Hi,
>
> 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.
>
> Thus the options now accepted are:
>
> \crosstabview [ [-|+]colV[:scolV] [-|+]colH[:scolH] [colG1[,colG2...]] ]
>
> 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
>