Re: [patch] Proposal for \crosstabview in psql

Dean Rasheed wrote:

> My biggest problem is with the sorting, for all the reasons discussed
> above. There is absolutely no reason for \crosstabview to be
> re-sorting rows -- they should just be left in the original query
> result order

We want the option to sort the vertical the header in a late additional
step when the ORDER BY of the query is already assigned to another
purpose.

I've submitted this example on the wiki:
https://wiki.postgresql.org/wiki/Crosstabview

create view v_data as
select * from ( values
('v1','h2','foo', '2015-04-01'::date),
('v2','h1','bar', '2015-01-02'),
('v1','h0','baz', '2015-07-12'),
('v0','h4','qux', '2015-07-15')
) as l(v,h,c,d);

Normal top-down display:

select v,to_char(d,'Mon') as m, c from v_data order by d;

v | m | c
----+-----+-----
v2 | Jan | bar
v1 | Apr | foo
v1 | Jul | baz
v0 | Jul | qux

Crosstabview display without any additional sort:

\crosstabview v m c

v | Jan | Apr | Jul
----+-----+-----+-----
v2 | bar | |
v1 | | foo | baz
v0 | | | qux

"d" is not present the resultset but it drives the sort
so that month names come out in the natural order.

\crosstabview does not discard the order of colH nor the order of colV,
it follows both, so that we get v2,v1,v0 in this order in the leftmost
column (vertical header) just like in the resultset.

At this point, it seems to me that it's perfectly reasonable for our user
to expect the possibility of sorting additionally by "v" , without
changing the query and without changing the order of the horizontal
header:

\crosstabview +v m c

v | Jan | Apr | Jul
----+-----+-----+-----
v0 | | | qux
v1 | | foo | baz
v2 | bar | |

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