Re: [patch] Proposal for \crosstabview in psql

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: "Dean Rasheed" <dean(dot)a(dot)rasheed(at)gmail(dot)com>,"Andres Freund" <andres(at)anarazel(dot)de>,"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>,"Alvaro Herrera" <alvherre(at)2ndquadrant(dot)com>,"Teodor Sigaev" <teodor(at)sigaev(dot)ru>,"PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [patch] Proposal for \crosstabview in psql
Date: 2016-03-12 15:34:07
Message-ID: b00796a3-c2d2-4a24-bf13-b4a678048f49@mm
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Robert Haas wrote:

> But worse than either of those things, there is no real
> agreement on what the overall design of this feature
> should be.

The part in the design that raised concerns upthread is
essentially how headers sorting is exposed to the user and

As suggested in [1], I've made some drastic changes in the
attached patch to take the comments (from Dean R., Tom L.)
into account. The idea is to limit to the bare minimum
the involvement of psql in sorting:

- the +/- syntax goes away

- the possibility of post-sorting the values through a backdoor
query goes away too, for both headers.

- the vertical order of the crosstab view is now driven solely by the
order in the query

- the order of the horizontal header can be optionally specified
by a column expected to contain an integer, with the syntax
\crosstabview colv colh:scolh [other cols]
which means "colh" will be sorted by "scolh".
It still defaults to whatever order "colh" comes in from the results

Concerning the optional "scolh", there are cases where it might pre-exist
naturally, such as a month number going in pair with a month name.
In other cases, a user may add it as a kind of "synthetic column"
by way of a window function, for example:
SELECT ...other columns...,
(row_number() over(order by something [order options]) as scolh
Only the relative order of scolh values is taken into account, the value
has no meaning for crosstabview.

- also NULLs are no longer excluded from headers, per Peter E.
comment in [2].



Best regards,
Daniel Vérité
PostgreSQL-powered mailer:
Twitter: @DanielVerite

Attachment Content-Type Size
psql-crosstabview-v12.diff text/x-patch 36.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-03-12 15:47:38 Re: Perl's newSViv() versus 64-bit ints?
Previous Message David G. Johnston 2016-03-12 14:44:20 Re: Performance improvement for joins where outer side is unique