Skip to content

Commit c09b18f

Browse files
alvherrealvherre
committed
Support \crosstabview in psql
\crosstabview is a completely different way to display results from a query: instead of a vertical display of rows, the data values are placed in a grid where the column and row headers come from the data itself, similar to a spreadsheet. The sort order of the horizontal header can be specified by using another column in the query, and the vertical header determines its ordering from the order in which they appear in the query. This only allows displaying a single value in each cell. If more than one value correspond to the same cell, an error is thrown. Merging of values can be done in the query itself, if necessary. This may be revisited in the future. Author: Daniel Verité Reviewed-by: Pavel Stehule, Dean Rasheed
1 parent 279d86a commit c09b18f

File tree

13 files changed

+1415
-27
lines changed

13 files changed

+1415
-27
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 141 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -989,6 +989,106 @@ testdb=>
989989
</listitem>
990990
</varlistentry>
991991

992+
<varlistentry>
993+
<term><literal>\crosstabview [
994+
<replaceable class="parameter">colV</replaceable>
995+
<replaceable class="parameter">colH</replaceable>
996+
[:<replaceable class="parameter">scolH</replaceable>]
997+
[<replaceable class="parameter">colD</replaceable>]
998+
] </literal></term>
999+
<listitem>
1000+
<para>
1001+
Execute the current query buffer (like <literal>\g</literal>) and shows
1002+
the results inside a crosstab grid.
1003+
The query must return at least three columns.
1004+
The output column <replaceable class="parameter">colV</replaceable>
1005+
becomes a vertical header
1006+
and the output column <replaceable class="parameter">colH</replaceable>
1007+
becomes a horizontal header, optionally sorted by ranking data obtained
1008+
from <replaceable class="parameter">scolH</replaceable>.
1009+
<replaceable class="parameter">colD</replaceable>
1010+
is the output column to project into the grid. If this is not
1011+
specified and there are exactly three columns in the result set,
1012+
the column that isn't
1013+
<replaceable class="parameter">colV</replaceable> nor
1014+
<replaceable class="parameter">colH</replaceable>
1015+
is displayed; if there are more columns, an error is thrown.
1016+
</para>
1017+
1018+
<para>
1019+
All columns can be refered to by their position (starting at 1), or by
1020+
their name. Normal case folding and quoting rules apply on column
1021+
names. By default,
1022+
<replaceable class="parameter">colV</replaceable> corresponds to column 1
1023+
and <replaceable class="parameter">colH</replaceable> to column 2.
1024+
A query having only one output column cannot be viewed in crosstab, and
1025+
<replaceable class="parameter">colH</replaceable> must differ from
1026+
<replaceable class="parameter">colV</replaceable>.
1027+
</para>
1028+
1029+
<para>
1030+
The vertical header, displayed as the leftmost column,
1031+
contains the deduplicated values found in
1032+
column <replaceable class="parameter">colV</replaceable>, in the same
1033+
order as in the query results.
1034+
</para>
1035+
1036+
<para>
1037+
The horizontal header, displayed as the first row,
1038+
contains the deduplicated values found in
1039+
column <replaceable class="parameter">colH</replaceable>, in
1040+
the order of appearance in the query results.
1041+
If specified, the optional <replaceable class="parameter">scolH</replaceable>
1042+
argument refers to a column whose values should be integer numbers
1043+
by which <replaceable class="parameter">colH</replaceable> will be sorted
1044+
to be positioned in the horizontal header.
1045+
</para>
1046+
1047+
<para>
1048+
Inside the crosstab grid,
1049+
given a query output with <literal>N</literal> columns
1050+
(including <replaceable class="parameter">colV</replaceable> and
1051+
<replaceable class="parameter">colH</replaceable>),
1052+
for each distinct value <literal>x</literal> of
1053+
<replaceable class="parameter">colH</replaceable>
1054+
and each distinct value <literal>y</literal> of
1055+
<replaceable class="parameter">colV</replaceable>,
1056+
the contents of a cell located at the intersection
1057+
<literal>(x,y)</literal> is determined by these rules:
1058+
<itemizedlist>
1059+
<listitem>
1060+
<para>
1061+
if there is no corresponding row in the query results such that the
1062+
value for <replaceable class="parameter">colH</replaceable>
1063+
is <literal>x</literal> and the value
1064+
for <replaceable class="parameter">colV</replaceable>
1065+
is <literal>y</literal>, the cell is empty.
1066+
</para>
1067+
</listitem>
1068+
1069+
<listitem>
1070+
<para>
1071+
if there is exactly one row such that the value
1072+
for <replaceable class="parameter">colH</replaceable>
1073+
is <literal>x</literal> and the value
1074+
for <replaceable class="parameter">colV</replaceable>
1075+
is <literal>y</literal>, then the <literal>colD</literal> column
1076+
is displayed.
1077+
</para>
1078+
</listitem>
1079+
1080+
<listitem>
1081+
<para>
1082+
if there are several such rows, an error is thrown.
1083+
</para>
1084+
</listitem>
1085+
1086+
</itemizedlist>
1087+
</para>
1088+
1089+
</listitem>
1090+
</varlistentry>
1091+
9921092
<varlistentry>
9931093
<term><literal>\d[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
9941094

@@ -4070,6 +4170,47 @@ first | 4
40704170
second | four
40714171
</programlisting></para>
40724172

4173+
<para>
4174+
When suitable, query results can be shown in a crosstab representation
4175+
with the \crosstabview command:
4176+
<programlisting>
4177+
testdb=&gt; <userinput>SELECT first, second, first &gt; 2 AS gt2 FROM my_table;</userinput>
4178+
first | second | ge2
4179+
-------+--------+-----
4180+
1 | one | f
4181+
2 | two | f
4182+
3 | three | t
4183+
4 | four | t
4184+
(4 rows)
4185+
4186+
testdb=&gt; <userinput>\crosstabview first second</userinput>
4187+
first | one | two | three | four
4188+
-------+-----+-----+-------+------
4189+
1 | f | | |
4190+
2 | | f | |
4191+
3 | | | t |
4192+
4 | | | | t
4193+
(4 rows)
4194+
</programlisting>
4195+
4196+
This second example shows a multiplication table with rows sorted in reverse
4197+
numerical order and columns with an independant, ascending numerical order.
4198+
<programlisting>
4199+
testdb=&gt; <userinput>SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",</userinput>
4200+
testdb(&gt; <userinput>row_number() over(order by t2.first) AS ord</userinput>
4201+
testdb(&gt; <userinput>FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC</userinput>
4202+
testdb(&gt; <userinput>\crosstabview A B:ord AxB</userinput>
4203+
A | 101 | 102 | 103 | 104
4204+
---+-----+-----+-----+-----
4205+
4 | 404 | 408 | 412 | 416
4206+
3 | 303 | 306 | 309 | 312
4207+
2 | 202 | 204 | 206 | 208
4208+
1 | 101 | 102 | 103 | 104
4209+
(4 rows)
4210+
</programlisting>
4211+
4212+
</para>
4213+
40734214
</refsect1>
40744215

40754216
</refentry>

‎src/bin/psql/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ LDFLAGS += -L$(top_builddir)/src/fe_utils -lpgfeutils -lpq
2323

2424
OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
2525
startup.o prompt.o variables.o large_obj.o describe.o \
26-
tab-complete.o \
26+
crosstabview.o tab-complete.o \
2727
sql_help.o psqlscanslash.o \
2828
$(WIN32RES)
2929

‎src/bin/psql/command.c

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@
3939

4040
#include "common.h"
4141
#include "copy.h"
42+
#include "crosstabview.h"
4243
#include "describe.h"
4344
#include "help.h"
4445
#include "input.h"
@@ -364,6 +365,20 @@ exec_command(const char *cmd,
364365
else if (strcmp(cmd, "copyright") == 0)
365366
print_copyright();
366367

368+
/* \crosstabview -- execute a query and display results in crosstab */
369+
else if (strcmp(cmd, "crosstabview") == 0)
370+
{
371+
pset.ctv_col_V = psql_scan_slash_option(scan_state,
372+
OT_NORMAL, NULL, false);
373+
pset.ctv_col_H = psql_scan_slash_option(scan_state,
374+
OT_NORMAL, NULL, false);
375+
pset.ctv_col_D = psql_scan_slash_option(scan_state,
376+
OT_NORMAL, NULL, false);
377+
378+
pset.crosstab_flag = true;
379+
status = PSQL_CMD_SEND;
380+
}
381+
367382
/* \d* commands */
368383
else if (cmd[0] == 'd')
369384
{

‎src/bin/psql/common.c

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@
2323
#include "settings.h"
2424
#include "command.h"
2525
#include "copy.h"
26+
#include "crosstabview.h"
2627
#include "fe_utils/mbprint.h"
2728

2829

@@ -1064,6 +1065,8 @@ PrintQueryResults(PGresult *results)
10641065
success = StoreQueryTuple(results);
10651066
else if (pset.gexec_flag)
10661067
success = ExecQueryTuples(results);
1068+
else if (pset.crosstab_flag)
1069+
success = PrintResultsInCrosstab(results);
10671070
else
10681071
success = PrintQueryTuples(results);
10691072
/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1213,7 +1216,8 @@ SendQuery(const char *query)
12131216
}
12141217
}
12151218

1216-
if (pset.fetch_count <= 0 || pset.gexec_flag || !is_select_command(query))
1219+
if (pset.fetch_count <= 0 || pset.gexec_flag ||
1220+
pset.crosstab_flag || !is_select_command(query))
12171221
{
12181222
/* Default fetch-it-all-and-print mode */
12191223
instr_time before,
@@ -1356,6 +1360,24 @@ SendQuery(const char *query)
13561360
/* reset \gexec trigger */
13571361
pset.gexec_flag = false;
13581362

1363+
/* reset \crosstabview trigger */
1364+
pset.crosstab_flag = false;
1365+
if (pset.ctv_col_V)
1366+
{
1367+
free(pset.ctv_col_V);
1368+
pset.ctv_col_V = NULL;
1369+
}
1370+
if (pset.ctv_col_H)
1371+
{
1372+
free(pset.ctv_col_H);
1373+
pset.ctv_col_H = NULL;
1374+
}
1375+
if (pset.ctv_col_D)
1376+
{
1377+
free(pset.ctv_col_D);
1378+
pset.ctv_col_D = NULL;
1379+
}
1380+
13591381
return OK;
13601382
}
13611383

@@ -1501,7 +1523,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
15011523
break;
15021524
}
15031525

1504-
/* Note we do not deal with \gexec mode here */
1526+
/* Note we do not deal with \gexec or \crosstabview modes here */
15051527

15061528
ntuples = PQntuples(results);
15071529

0 commit comments

Comments
 (0)