> postgres=# \crosstabview 4 +month label > > Maybe using optional int order column instead label is better - then you can > do sort on client side > > so the syntax can be "\crosstabview VCol [+/-]HCol [[+-]HOrderCol]
In the meantime I've followed a different idea: allowing the vertical header to be sorted too, still server-side.
That's because to me, the first impulse for a user noticing that it's not sorted vertically would be to write \crosstabview +customer month rather than figure out the \crosstabview customer +month_number month_name invocation. But both ways aren't even mutually exclusive. We could support \crosstabview [+|-]colV[:labelV] [+|-]colH[:labelH] it's more complicated to understand, but not harder to implement.
yes, I was able to do what I would - although the query was little bit strange
select amount, label, customer from (select sum(amount) as amount, extract(month from closed)::int - 1 as Month, to_char(date_trunc('month', closed), 'TMmon') as label, customer from data group by customer, to_char(date_trunc('month', closed), 'TMmon'), extract(month from closed) union select sum(amount), extract(month from closed)::int - 1 as month, to_char(date_trunc('month', closed), 'TMmon') as label, '**** TOTAL ****' from data group by to_char(date_trunc('month', closed), 'TMmon'), extract(month from closed)::int - 1 order by month) x
\crosstabview +3 2
Also, a non-zero FETCH_COUNT is supported by this version of the patch, if the first internal FETCH retrieves less than FETCH_COUNT rows. Otherwise a specific error is emitted.
good idea
Also there are minor changes in arguments and callers following recent code changes for \o
Trying to crosstab with 10k+ distinct values vertically, I've noticed that the current code is too slow, spending too much time sorting. I'm currently replacing its simple arrays of distinct values with AVL binary trees, which I expect to be much more efficient for this.
Regards
Pavel
Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite