Re: [patch] Proposal for \crosstabview in psql

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
implemented.

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
FROM...
Only the relative order of scolh values is taken into account, the value
itself
has no meaning for crosstabview.

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

[1]
http://www.postgresql.org/message-id/3d513263-104b-41e3-b1c7-4ad4bd99c491@mm

[2] http://www.postgresql.org/message-id/56C4E344.6070903@gmx.net

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