[patch] A \pivot command for psql

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [patch] A \pivot command for psql
Date: 2015-08-09 17:29:40
Message-ID: 78543039-c708-4f5d-a66f-0c0fbcda1f76@mm
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I want to suggest a client-side \pivot command in psql, implemented
in the attached patch.

\pivot takes the current query in the buffer, execute it and
display it pivoted by interpreting the result as:

column1 => row in pivoted output
column2 => column in pivoted output
column3 => value at (row,column) in pivoted ouput

The advantage over the server-side crosstab() from contrib is in
ease of use, mostly because \pivot doesn't need in advance the
list of columns that result from pivoting.

Typical use cases are queries that produce values
along two axes:
SELECT a,b,aggr(something) FROM tbl GROUP a,b;
\pivot displays immediately the matrix-like representation

Or displaying "pairing" between columns, as in
SELECT a,b,'X' FROM tblA [LEFT|RIGHT|FULL] JOIN tblB...
which once pivoted shows in an easily readable way
what "a" is/isn't in relation with any "b".

Columns are sorted with strcmp(). I think a more adequate sort could
be obtained through a separate query with ORDER BY just for these
values (casted to their original type), but the patch doesn't do that
yet.

Also, \pivot could take optionally the query as an argument instead
of getting it only from the query buffer.

Anyway, does it look useful enough to be part of psql?
I guess I should push this to commitfest if that's the case.

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

Attachment Content-Type Size
psql-pivot.diff text/x-patch 6.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2015-08-09 17:37:51 Re: [patch] A \pivot command for psql
Previous Message Tom Lane 2015-08-09 17:23:52 Re: Assert in pg_stat_statements