Re: [patch] Proposal for \crosstabview in psql

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: "PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Cc: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Subject: Re: [patch] Proposal for \crosstabview in psql
Date: 2016-01-22 18:53:57
Message-ID: 9568f288-d3cc-4f23-b05b-fe785025791a@mm
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


Here's an updated patch improving on how the horizontal and vertical
headers can be sorted.

The discussion upthread went into how it was desirable
to have independant sorts for these headers, possibly driven
by another column, in addition to the query's ORDER BY.

Thus the options now accepted are:

\crosstabview [ [-|+]colV[:scolV] [-|+]colH[:scolH] [colG1[,colG2...]] ]

The optional scolV/scolH columns drive sorts for respectively
colV/colH (colV:scolV somehow means SELECT colV from... order by scolV)

colG1,... in 3rd arg indicate the columns whose contents form the grid
cells, the typical use case being that there's only one such column.
By default it's all columns minus colV and colH.

For example,

date_part('month, sales_date),
to_char(sales_date, 'Mon') as month,
FROM sales_view
WHERE [predicates]
[ORDER BY ...]

If we want to look at <amount> in a grid with months names across, sorted
by month number, and customer name in the vertical header, sorted by date of
acquisition, we could do this:

\crosstabview +cust_name:cust_date +5:4 amount

or letting the vertical header being sorted by the query's ORDER BY,
and the horizontal header same as above:

\crosstabview cust_name +5:4 amount

or sorting vertically by name, if it happens that the ORDER BY is missing or
is on something else:

\crosstabview +cust_name +5:4 amount

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

Attachment Content-Type Size
psql-crosstabview-v10.diff text/x-patch 40.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Victor Wagner 2016-01-22 19:30:51 Re: Patch: Implement failover on libpq connect level.
Previous Message Corey Huinker 2016-01-22 18:42:01 Re: Declarative partitioning