Re: [patch] Proposal for \rotate in psql

2015-12-14 23:09 GMT+01:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:

> Pavel Stehule wrote:
>
> > 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
>