Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc09b18f

Browse files
committed
Support \crosstabview in psql
\crosstabview is a completely different way to display results from aquery: instead of a vertical display of rows, the data values are placedin 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 usinganother column in the query, and the vertical header determines itsordering from the order in which they appear in the query.This only allows displaying a single value in each cell. If more thanone value correspond to the same cell, an error is thrown. Merging ofvalues can be done in the query itself, if necessary. This may berevisited in the future.Author: Daniel VeritéReviewed-by: Pavel Stehule, Dean Rasheed
1 parent279d86a commitc09b18f

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.otab-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
elseif (strcmp(cmd,"copyright")==0)
365366
print_copyright();
366367

368+
/* \crosstabview -- execute a query and display results in crosstab */
369+
elseif (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
elseif (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
elseif (pset.gexec_flag)
10661067
success=ExecQueryTuples(results);
1068+
elseif (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_timebefore,
@@ -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
returnOK;
13601382
}
13611383

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

1504-
/* Note we do not deal with \gexecmode here */
1526+
/* Note we do not deal with \gexecor \crosstabview modes here */
15051527

15061528
ntuples=PQntuples(results);
15071529

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp