Pavel Stehule wrote:
> [ \rotate being a wrong name ]
Here's an updated patch.
First it renames the command to \crosstabview, which hopefully may
be more consensual, at least it's semantically much closer to crosstab .
> The important question is sorting output. The vertical header is
> sorted by first appearance in result. The horizontal header is
> sorted in ascending or descending order. This is unfriendly for
> often use case - month names. This can be solved by third parameter
> - sort function.
I've thought that sorting with an external function would be too
complicated for this command, but sorting ascending by default
was not the right choice either.
So I've changed to sorting by first appearance in result (like the vertical
header), and sorting ascending or descending only when specified
(with +colH or -colH syntax).
So the synopsis becomes: \crosstabview [ colV [+ | -]colH ]
Example with a time series (daily mean temperatures in Paris,2014),
month names across, day numbers down :
select
to_char(w_date,'DD') as day ,
to_char(w_date,'Mon') as month,
w_temp from weather
where w_date between '2014-01-01' and '2014-12-31'
order by w_date
\crosstabview
day | Jan | Feb | Mar | Apr | May | Jun | ...[cut]
-----+-----+-----+-----+-----+-----+-----+-
01 | 8 | 8 | 6 | 16 | 12 | 15 |
02 | 10 | 6 | 6 | 15 | 12 | 16 |
03 | 11 | 5 | 7 | 14 | 11 | 17 |
04 | 10 | 6 | 8 | 12 | 12 | 14 |
05 | 6 | 7 | 8 | 14 | 16 | 14 |
06 | 10 | 9 | 9 | 16 | 17 | 20 |
07 | 11 | 10 | 10 | 18 | 14 | 24 |
08 | 11 | 8 | 12 | 10 | 13 | 22 |
09 | 10 | 6 | 14 | 12 | 16 | 22 |
10 | 6 | 7 | 14 | 14 | 14 | 19 |
11 | 7 | 6 | 12 | 14 | 12 | 21 |
...cut..
28 | 4 | 7 | 10 | 12 | 14 | 16 |
29 | 4 | | 14 | 10 | 15 | 16 |
30 | 5 | | 14 | 14 | 17 | 18 |
31 | 5 | | 14 | | 16 | |
The month names come out in the expected order here,
contrary to what happened with the previous iteration of
the patch which forced a sort in all cases.
Here it plays out well because the single "ORDER BY w_date" is
simultaneously OK for the vertical and horizontal headers,
a common case for time series.
For more complicated cases, when the horizontal and vertical
headers should be ordered independantly, and
in addition the horizontal header should not be sorted
by its values, I've toyed with the idea of sorting by another
column which would supposedly be added in the query
just for sorting, but it loses much in simplicity. For the more
complex stuff, users can always turn to the server-side methods
if needed.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 5899bb4..3836fbd 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2449,6 +2449,95 @@ lo_import 152801
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\crosstabview [ <replaceable class="parameter">colV</replaceable> [-|+]<replaceable class="parameter">colH</replaceable> ] </literal></term>
+ <listitem>
+ <para>
+ Execute the current query buffer (like <literal>\g</literal>) and shows the results
+ inside a crosstab grid. The output column <replaceable class="parameter">colV</replaceable>
+ becomes a vertical header and the output column
+ <replaceable class="parameter">colH</replaceable> becomes a horizontal header.
+ The results for the other output columns are projected inside the grid.
+ </para>
+
+ <para>
+ <replaceable class="parameter">colV</replaceable>
+ and <replaceable class="parameter">colH</replaceable> can indicate a
+ column position (starting at 1), or a column name. Normal case folding
+ and quoting rules apply on column names. By default,
+ <replaceable class="parameter">colV</replaceable> is column 1
+ and <replaceable class="parameter">colH</replaceable> is column 2.
+ A query having only one output column cannot be viewed in crosstab, and
+ <replaceable class="parameter">colH</replaceable> must differ from
+ <replaceable class="parameter">colV</replaceable>.
+ </para>
+
+ <para>
+ The vertical header, displayed as the leftmost column,
+ contains the set of all distinct values found in
+ column <replaceable class="parameter">colV</replaceable>, in the order
+ of their first appearance in the query results.
+ </para>
+ <para>
+ The horizontal header, displayed as the first row,
+ contains the set of all distinct non-null values found in
+ column <replaceable class="parameter">colH</replaceable>. They come
+ by default in their order of appearance in the query results, or in ascending
+ order if a plus (+) sign precedes <replaceable class="parameter">colH</replaceable>,
+ or in descending order if it's a minus (-) sign.
+ </para>
+
+ <para>
+ The query results being tuples of <literal>N</literal> columns
+ (including <replaceable class="parameter">colV</replaceable> and
+ <replaceable class="parameter">colH</replaceable>),
+ for each distinct value <literal>x</literal> of
+ <replaceable class="parameter">colH</replaceable>
+ and each distinct value <literal>y</literal> of
+ <replaceable class="parameter">colV</replaceable>,
+ a cell located at the intersection <literal>(x,y)</literal> in the grid
+ has contents determined by these rules:
+ <itemizedlist>
+ <listitem>
+ <para>
+ if there is no corresponding row in the results such that the value
+ for <replaceable class="parameter">colH</replaceable>
+ is <literal>x</literal> and the value
+ for <replaceable class="parameter">colV</replaceable>
+ is <literal>y</literal>, the cell is empty.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ if there is exactly one row such that the value
+ for <replaceable class="parameter">colH</replaceable>
+ is <literal>x</literal> and the value
+ for <replaceable class="parameter">colV</replaceable>
+ is <literal>y</literal>, then the <literal>N-2</literal> other
+ columns are displayed in the cell, separated between each other by
+ a space character if needed.
+
+ If <literal>N=2</literal>, the letter <literal>X</literal> is displayed in the cell as
+ if a virtual third column contained that character.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ if there are several corresponding rows, the behavior is identical to one row
+ except that the values coming from different rows are stacked
+ vertically, rows being separated by newline characters inside
+ the same cell.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index f1336d5..9cb0c4a 100644
--- a/src/bin/psql/Makefile
+++ b/src/bin/psql/Makefile
@@ -23,7 +23,7 @@ override CPPFLAGS := -I. -I$(srcdir) -I$(libpq_srcdir) -I$(top_srcdir)/src/bin/p
OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
startup.o prompt.o variables.o large_obj.o print.o describe.o \
tab-complete.o mbprint.o dumputils.o keywords.o kwlookup.o \
- sql_help.o \
+ sql_help.o crosstabview.o \
$(WIN32RES)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 438a4ec..56f37a6 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -46,6 +46,7 @@
#include "mainloop.h"
#include "print.h"
#include "psqlscan.h"
+#include "crosstabview.h"
#include "settings.h"
#include "variables.h"
@@ -1081,6 +1082,34 @@ exec_command(const char *cmd,
free(pw2);
}
+ /* \crosstabview -- execute a query and display results in crosstab */
+ else if (strcmp(cmd, "crosstabview") == 0)
+ {
+ char *opt1,
+ *opt2;
+
+ opt1 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+ opt2 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (opt1 && !opt2)
+ {
+ psql_error(_("\\%s: missing second argument\n"), cmd);
+ success = false;
+ }
+ else
+ {
+ pset.crosstabview_col_V = opt1 ? pg_strdup(opt1): NULL;
+ pset.crosstabview_col_H = opt2 ? pg_strdup(opt2): NULL;
+ pset.crosstabview_output = true;
+ status = PSQL_CMD_SEND;
+ }
+
+ free(opt1);
+ free(opt2);
+ }
+
/* \prompt -- prompt and set variable */
else if (strcmp(cmd, "prompt") == 0)
{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 0e266a3..4329fdc 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -23,6 +23,7 @@
#include "command.h"
#include "copy.h"
#include "mbprint.h"
+#include "crosstabview.h"
@@ -1061,7 +1062,14 @@ SendQuery(const char *query)
/* but printing results isn't: */
if (OK && results)
- OK = PrintQueryResults(results);
+ {
+ if (pset.crosstabview_output)
+ OK = PrintResultsInCrossTab(results,
+ pset.crosstabview_col_V,
+ pset.crosstabview_col_H);
+ else
+ OK = PrintQueryResults(results);
+ }
}
else
{
@@ -1177,6 +1185,17 @@ sendquery_cleanup:
pset.gset_prefix = NULL;
}
+ pset.crosstabview_output = false;
+ if (pset.crosstabview_col_V)
+ {
+ free(pset.crosstabview_col_V);
+ pset.crosstabview_col_V = NULL;
+ }
+ if (pset.crosstabview_col_H)
+ {
+ free(pset.crosstabview_col_H);
+ pset.crosstabview_col_H = NULL;
+ }
return OK;
}
diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
new file mode 100644
index 0000000..08b4203
--- /dev/null
+++ b/src/bin/psql/crosstabview.c
@@ -0,0 +1,608 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2000-2015, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/crosstabview.c
+ */
+
+#include "common.h"
+#include "pqexpbuffer.h"
+#include "crosstabview.h"
+#include "settings.h"
+
+#include <string.h>
+
+static int
+headerCompare(const void *a, const void *b)
+{
+ return strcmp( ((struct pivot_field*)a)->name,
+ ((struct pivot_field*)b)->name);
+}
+
+static void
+accumHeader(char* name, int* count, struct pivot_field **sorted_tab, int row_number)
+{
+ struct pivot_field *p;
+
+ /*
+ * Search for name in sorted_tab. If it doesn't exist, insert it,
+ * otherwise do nothing.
+ */
+
+ if (*count >= 1)
+ {
+ p = (struct pivot_field*) bsearch(&name,
+ *sorted_tab,
+ *count,
+ sizeof(struct pivot_field),
+ headerCompare);
+ }
+ else
+ p=NULL;
+
+ if (!p)
+ {
+ *sorted_tab = pg_realloc(*sorted_tab, sizeof(struct pivot_field) * (1+*count));
+ (*sorted_tab)[*count].name = name;
+ (*sorted_tab)[*count].rank = *count;
+ (*count)++;
+
+ qsort(*sorted_tab,
+ *count,
+ sizeof(struct pivot_field),
+ headerCompare);
+ }
+}
+
+/*
+ * Send a query to sort all column values cast to the Oid passed in a VALUES clause
+ */
+static bool
+sortColumns(Oid coltype, struct pivot_field *columns, int nb_cols, int direction)
+{
+ bool retval = false;
+ PGresult *res = NULL;
+ PQExpBufferData query;
+ int i;
+ Oid *param_types;
+ const char** param_values;
+ int* param_lengths;
+ int* param_formats;
+
+ if (nb_cols < 2 || direction==0)
+ return true; /* nothing to sort */
+
+ param_types = (Oid*) pg_malloc(nb_cols*sizeof(Oid));
+ param_values = (const char**) pg_malloc(nb_cols*sizeof(char*));
+ param_lengths = (int*) pg_malloc(nb_cols*sizeof(int));
+ param_formats = (int*) pg_malloc(nb_cols*sizeof(int));
+
+ initPQExpBuffer(&query);
+
+ /*
+ * The query returns the original position of each value in our list,
+ * ordered by its new position. The value itself is not returned.
+ */
+ appendPQExpBuffer(&query, "SELECT n FROM (VALUES");
+
+ for (i=1; i <= nb_cols; i++)
+ {
+ if (i < nb_cols)
+ appendPQExpBuffer(&query, "($%d,%d),", i, i);
+ else
+ {
+ appendPQExpBuffer(&query, "($%d,%d)) AS l(x,n) ORDER BY x", i, i);
+ if (direction < 0)
+ appendPQExpBuffer(&query, " DESC");
+ }
+
+ param_types[i-1] = coltype;
+ param_values[i-1] = columns[i-1].name;
+ param_lengths[i-1] = strlen(columns[i-1].name);
+ param_formats[i-1] = 0;
+ }
+
+ res = PQexecParams(pset.db,
+ query.data,
+ nb_cols,
+ param_types,
+ param_values,
+ param_lengths,
+ param_formats,
+ 0);
+
+ if (res)
+ {
+ ExecStatusType status = PQresultStatus(res);
+ if (status == PGRES_TUPLES_OK)
+ {
+ for (i=0; i < PQntuples(res); i++)
+ {
+ int old_pos = atoi(PQgetvalue(res, i, 0));
+
+ if (old_pos < 1 || old_pos > nb_cols || i >= nb_cols)
+ {
+ /*
+ * A position outside of the range is normally impossible.
+ * If this happens, we're facing a malfunctioning or hostile
+ * server or middleware.
+ */
+ psql_error(_("Unexpected value when sorting horizontal headers"));
+ goto cleanup;
+ }
+ else
+ {
+ columns[old_pos-1].rank = i;
+ }
+ }
+ }
+ else
+ {
+ psql_error(_("Query error when sorting horizontal headers: %s"),
+ PQerrorMessage(pset.db));
+ goto cleanup;
+ }
+ }
+
+ retval = true;
+
+cleanup:
+ termPQExpBuffer(&query);
+ if (res)
+ PQclear(res);
+ pg_free(param_types);
+ pg_free(param_values);
+ pg_free(param_lengths);
+ pg_free(param_formats);
+ return retval;
+}
+
+static void
+printCrosstab(const PGresult *results,
+ int num_columns,
+ struct pivot_field *piv_columns,
+ int field_for_columns,
+ int num_rows,
+ struct pivot_field *piv_rows,
+ int field_for_rows)
+{
+ printQueryOpt popt = pset.popt;
+ printTableContent cont;
+ int i, j, rn;
+ char col_align = 'l'; /* alignment for values inside the grid */
+ int* horiz_map; /* map indices from sorted horizontal headers to piv_columns */
+ char** allocated_cells; /* Pointers for cell contents that are allocated
+ * in this function, when cells cannot simply point to
+ * PQgetvalue(results, ...) */
+
+ printTableInit(&cont, &popt.topt, popt.title, num_columns+1, num_rows);
+
+ /* Step 1: set target column names (horizontal header) */
+
+ /* The name of the first column is kept unchanged by the pivoting */
+ printTableAddHeader(&cont,
+ PQfname(results, field_for_rows),
+ false,
+ column_type_alignment(PQftype(results, field_for_rows)));
+
+ /*
+ * To iterate over piv_columns[] by piv_columns[].rank, create a reverse map
+ * associating each piv_columns[].rank to its index in piv_columns.
+ * This avoids an O(N^2) loop later
+ */
+ horiz_map = (int*) pg_malloc(sizeof(int) * num_columns);
+ for (i = 0; i < num_columns; i++)
+ {
+ horiz_map[piv_columns[i].rank] = i;
+ }
+
+ /*
+ * In the case of 3 output columns, the contents in the cells are exactly
+ * the contents of the "value" column (3rd column by default), so their
+ * alignment is determined by PQftype(). Otherwise the contents are
+ * made-up strings, so the alignment is 'l'
+ */
+ if (PQnfields(results) == 3)
+ {
+ int colnum; /* column placed inside the grid */
+ /*
+ * find colnum in the permutations of (0,1,2) where colnum is
+ * neither field_for_rows nor field_for_columns
+ */
+ switch (field_for_rows)
+ {
+ case 0:
+ colnum = (field_for_columns == 1) ? 2 : 1;
+ break;
+ case 1:
+ colnum = (field_for_columns == 0) ? 2: 0;
+ break;
+ default: /* should be always 2 */
+ colnum = (field_for_columns == 0) ? 1: 0;
+ break;
+ }
+ col_align = column_type_alignment(PQftype(results, colnum));
+ }
+ else
+ col_align = 'l';
+
+ for (i = 0; i < num_columns; i++)
+ {
+ printTableAddHeader(&cont,
+ piv_columns[horiz_map[i]].name,
+ false,
+ col_align);
+ }
+ pg_free(horiz_map);
+
+ /* Step 2: set row names in the first output column (vertical header) */
+ for (i = 0; i < num_rows; i++)
+ {
+ int k = piv_rows[i].rank;
+ cont.cells[k*(num_columns+1)] = piv_rows[i].name;
+ /* Initialize all cells inside the grid to an empty value */
+ for (j = 0; j < num_columns; j++)
+ cont.cells[k*(num_columns+1)+j+1] = "";
+ }
+ cont.cellsadded = num_rows * (num_columns+1);
+
+ allocated_cells = (char**) pg_malloc0(num_rows * num_columns * sizeof(char*));
+
+ /* Step 3: set all the cells "inside the grid" */
+ for (rn = 0; rn < PQntuples(results); rn++)
+ {
+ char* row_name;
+ char* col_name;
+ int row_number;
+ int col_number;
+ struct pivot_field *p;
+
+ row_number = col_number = -1;
+ /* Find target row */
+ if (!PQgetisnull(results, rn, field_for_rows))
+ {
+ row_name = PQgetvalue(results, rn, field_for_rows);
+ p = (struct pivot_field*) bsearch(&row_name,
+ piv_rows,
+ num_rows,
+ sizeof(struct pivot_field),
+ headerCompare);
+ if (p)
+ row_number = p->rank;
+ }
+
+ /* Find target column */
+ if (!PQgetisnull(results, rn, field_for_columns))
+ {
+ col_name = PQgetvalue(results, rn, field_for_columns);
+ p = (struct pivot_field*) bsearch(&col_name,
+ piv_columns,
+ num_columns,
+ sizeof(struct pivot_field),
+ headerCompare);
+ if (p)
+ col_number = p->rank;
+ }
+
+ /* Place value into cell */
+ if (col_number>=0 && row_number>=0)
+ {
+ int idx = 1 + col_number + row_number*(num_columns+1);
+ int src_col = 0; /* column number in source result */
+ int k = 0;
+
+ do {
+ char *content;
+
+ if (PQnfields(results) == 2)
+ {
+ /*
+ special case: when the source has only 2 columns, use a
+ X (cross/checkmark) for the cell content, and set
+ src_col to a virtual additional column.
+ */
+ content = "X";
+ src_col = 3;
+ }
+ else if (src_col == field_for_rows || src_col == field_for_columns)
+ {
+ /*
+ The source values that produce headers are not processed
+ in this loop, only the values that end up inside the grid.
+ */
+ src_col++;
+ continue;
+ }
+ else
+ {
+ content = (!PQgetisnull(results, rn, src_col)) ?
+ PQgetvalue(results, rn, src_col) :
+ (popt.nullPrint ? popt.nullPrint : "");
+ }
+
+ if (cont.cells[idx] != NULL && cont.cells[idx][0] != '\0')
+ {
+ /*
+ * Multiple values for the same (row,col) are projected
+ * into the same cell. When this happens, separate the
+ * previous content of the cell from the new value by a
+ * newline.
+ */
+ int content_size =
+ strlen(cont.cells[idx])
+ + 2 /* room for [CR],LF or space */
+ + strlen(content)
+ + 1; /* '\0' */
+ char *new_content;
+
+ /*
+ * idx2 is an index into allocated_cells. It differs from
+ * idx (index into cont.cells), because vertical and
+ * horizontal headers are included in `cont.cells` but
+ * excluded from allocated_cells.
+ */
+ int idx2 = (row_number * num_columns) + col_number;
+
+ if (allocated_cells[idx2] != NULL)
+ {
+ new_content = pg_realloc(allocated_cells[idx2], content_size);
+ }
+ else
+ {
+ /*
+ * At this point, cont.cells[idx] still contains a
+ * PQgetvalue() pointer. Just after, it will contain
+ * a new pointer maintained in allocated_cells[], and
+ * freed at the end of this function.
+ */
+ new_content = pg_malloc(content_size);
+ strcpy(new_content, cont.cells[idx]);
+ }
+ cont.cells[idx] = new_content;
+ allocated_cells[idx2] = new_content;
+
+ /*
+ * Contents that are on adjacent columns in the source results get
+ * separated by one space in the target.
+ * Contents that are on different rows in the source get
+ * separated by newlines in the target.
+ */
+ if (k==0)
+ strcat(new_content, "\n");
+ else
+ strcat(new_content, " ");
+ strcat(new_content, content);
+ }
+ else
+ {
+ cont.cells[idx] = content;
+ }
+ k++;
+ src_col++;
+ } while (src_col < PQnfields(results));
+ }
+ }
+
+ printTable(&cont, pset.queryFout, pset.logfile);
+ printTableCleanup(&cont);
+
+
+ for (i=0; i < num_rows * num_columns; i++)
+ {
+ if (allocated_cells[i] != NULL)
+ pg_free(allocated_cells[i]);
+ }
+
+ pg_free(allocated_cells);
+}
+
+/*
+ * Compare a user-supplied argument against a field name obtained by PQfname(),
+ * which is already case-folded.
+ * If arg is not enclosed in double quotes, pg_strcasecmp applies, otherwise
+ * do a case-sensitive comparison with these rules:
+ * - double quotes enclosing 'arg' are filtered out
+ * - double quotes inside 'arg' are expected to be doubled
+ */
+static int
+fieldnameCmp(const char* arg, const char* fieldname)
+{
+ const unsigned char* p = (const unsigned char*) arg;
+ const unsigned char* f = (const unsigned char*) fieldname;
+ unsigned char c;
+
+ if (*p++ != '"')
+ return pg_strcasecmp(arg, fieldname);
+
+ while ((c=*p++))
+ {
+ if (c=='"')
+ {
+ if (*p=='"')
+ p++; /* skip second quote and continue */
+ else if (*p=='\0')
+ return *p-*f; /* p finishes before f or is identical */
+
+ }
+ if (*f=='\0')
+ return 1; /* f finishes before p */
+ if (c!=*f)
+ return c-*f;
+ f++;
+ }
+ return (*f=='\0') ? 0 : 1;
+}
+
+
+/*
+ * arg can be a number or a column name, possibly quoted (like in an ORDER BY clause)
+ * Returns:
+ * on success, the 0-based index of the column
+ * or -1 if the column number or name is not found in the result's structure,
+ * or if it's ambiguous (arg corresponding to several columns)
+ */
+static int
+indexOfColumn(const char* arg, PGresult* res)
+{
+ int idx;
+
+ if (strspn(arg, "0123456789") == strlen(arg))
+ {
+ /* if arg contains only digits, it's a column number */
+ idx = atoi(arg) - 1;
+ if (idx < 0 || idx >= PQnfields(res))
+ {
+ psql_error(_("Invalid column number: %s\n"), arg);
+ return -1;
+ }
+ }
+ else
+ {
+ int i;
+ idx = -1;
+ for (i=0; i < PQnfields(res); i++)
+ {
+ if (fieldnameCmp(arg, PQfname(res, i)) == 0)
+ {
+ if (idx>=0)
+ {
+ /* if another idx was already found for the same name */
+ psql_error(_("Ambiguous column name: %s\n"), arg);
+ return -1;
+ }
+ idx = i;
+ }
+ }
+ if (idx == -1)
+ {
+ psql_error(_("Invalid column name: %s\n"), arg);
+ return -1;
+ }
+ }
+ return idx;
+}
+
+bool
+PrintResultsInCrossTab(PGresult* res,
+ const char* opt_field_for_rows, /* COLV or null */
+ const char* opt_field_for_columns) /* [-+]COLH or null */
+{
+ int rn;
+ struct pivot_field *piv_columns = NULL;
+ struct pivot_field *piv_rows = NULL;
+ int num_columns = 0;
+ int num_rows = 0;
+ bool retval = false;
+ int columns_sort_direction = 0; /* 1:ascending, 0:none, -1:descending */
+
+ /* 0-based index of the field whose distinct values will become COLUMN headers */
+ int field_for_columns;
+
+ /* 0-based index of the field whose distinct values will become ROW headers */
+ int field_for_rows;
+
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ goto error_return;
+
+ if (PQnfields(res) < 2)
+ {
+ psql_error(_("The query must return at least two columns to be shown in crosstab\n"));
+ goto error_return;
+ }
+
+ field_for_rows = (opt_field_for_rows != NULL)
+ ? indexOfColumn(opt_field_for_rows, res)
+ : 0;
+
+ if (field_for_rows < 0)
+ goto error_return;
+
+ if (opt_field_for_columns == NULL)
+ field_for_columns = 1;
+ else
+ {
+ /*
+ * descending sort is requested if the column reference is
+ * preceded with a minus sign
+ */
+ if (*opt_field_for_columns == '-')
+ {
+ columns_sort_direction = -1;
+ opt_field_for_columns++;
+ }
+ else if (*opt_field_for_columns == '+')
+ {
+ columns_sort_direction = 1;
+ opt_field_for_columns++;
+ }
+ field_for_columns = indexOfColumn(opt_field_for_columns, res);
+ if (field_for_columns < 0)
+ goto error_return;
+ }
+
+
+ if (field_for_columns == field_for_rows)
+ {
+ psql_error(_("The same column cannot be used for both vertical and horizontal headers\n"));
+ goto error_return;
+ }
+
+ /*
+ * First pass: accumulate row names and column names, each into their
+ * array. Use client-side sort but only to build the set of DISTINCT
+ * values. The final order displayed depends only on server-side
+ * sorts.
+ */
+ for (rn = 0; rn < PQntuples(res); rn++)
+ {
+ if (!PQgetisnull(res, rn, field_for_rows))
+ {
+ accumHeader(PQgetvalue(res, rn, field_for_rows),
+ &num_rows,
+ &piv_rows,
+ rn);
+ }
+
+ if (!PQgetisnull(res, rn, field_for_columns))
+ {
+ accumHeader(PQgetvalue(res, rn, field_for_columns),
+ &num_columns,
+ &piv_columns,
+ rn);
+ if (num_columns > 1600)
+ {
+ psql_error(_("Maximum number of columns (1600) exceeded\n"));
+ goto error_return;
+ }
+ }
+ }
+
+ /*
+ * Second pass: sort the list of target columns on the server.
+ */
+ if (!sortColumns(PQftype(res, field_for_columns),
+ piv_columns,
+ num_columns,
+ columns_sort_direction))
+ goto error_return;
+
+ /*
+ * Third pass: print the crosstab'ed results.
+ */
+ printCrosstab(res,
+ num_columns,
+ piv_columns,
+ field_for_columns,
+ num_rows,
+ piv_rows,
+ field_for_rows);
+
+ retval = true;
+
+error_return:
+ pg_free(piv_columns);
+ pg_free(piv_rows);
+
+ return retval;
+}
diff --git a/src/bin/psql/crosstabview.h b/src/bin/psql/crosstabview.h
new file mode 100644
index 0000000..c18efef
--- /dev/null
+++ b/src/bin/psql/crosstabview.h
@@ -0,0 +1,33 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2000-2015, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/crosstabview.h
+ */
+
+#ifndef CROSSTABVIEW_H
+#define CROSSTABVIEW_H
+
+struct pivot_field
+{
+ /* Pointer obtained from PGgetvalue() for colV or colH */
+ char* name;
+
+ /* Rank of the field in its list, starting at 0.
+ * - For headers stacked vertically, rank=N means it's the
+ * Nth distinct field encountered when looping through rows
+ * in their initial order.
+ * - For headers stacked horizontally, rank is obtained
+ * by server-side sorting in sortColumns()
+ */
+ int rank;
+};
+
+/* prototypes */
+extern bool
+PrintResultsInCrossTab(PGresult* res,
+ const char* opt_field_for_rows,
+ const char* opt_field_for_columns);
+
+#endif /* CROSSTABVIEW_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 5b63e76..c38d51d 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -175,6 +175,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n"));
fprintf(output, _(" \\gset [PREFIX] execute query and store results in psql variables\n"));
fprintf(output, _(" \\q quit psql\n"));
+ fprintf(output, _(" \\crosstabview [V H] execute query and display results in crosstab\n"));
fprintf(output, _(" \\watch [SEC] execute query every SEC seconds\n"));
fprintf(output, "\n");
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
index ad4350e..08d80db 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -3170,30 +3170,9 @@ printQuery(const PGresult *result, const printQueryOpt *opt, FILE *fout, FILE *f
for (i = 0; i < cont.ncolumns; i++)
{
- char align;
- Oid ftype = PQftype(result, i);
-
- switch (ftype)
- {
- case INT2OID:
- case INT4OID:
- case INT8OID:
- case FLOAT4OID:
- case FLOAT8OID:
- case NUMERICOID:
- case OIDOID:
- case XIDOID:
- case CIDOID:
- case CASHOID:
- align = 'r';
- break;
- default:
- align = 'l';
- break;
- }
-
printTableAddHeader(&cont, PQfname(result, i),
- opt->translate_header, align);
+ opt->translate_header,
+ column_type_alignment(PQftype(result, i)));
}
/* set cells */
@@ -3235,6 +3214,31 @@ printQuery(const PGresult *result, const printQueryOpt *opt, FILE *fout, FILE *f
printTableCleanup(&cont);
}
+char
+column_type_alignment(Oid ftype)
+{
+ char align;
+
+ switch (ftype)
+ {
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ case OIDOID:
+ case XIDOID:
+ case CIDOID:
+ case CASHOID:
+ align = 'r';
+ break;
+ default:
+ align = 'l';
+ break;
+ }
+ return align;
+}
void
setDecimalLocale(void)
diff --git a/src/bin/psql/print.h b/src/bin/psql/print.h
index b0b6bf5..db6dfb5 100644
--- a/src/bin/psql/print.h
+++ b/src/bin/psql/print.h
@@ -171,7 +171,7 @@ extern FILE *PageOutput(int lines, const printTableOpt *topt);
extern void ClosePager(FILE *pagerpipe);
extern void html_escaped_print(const char *in, FILE *fout);
-
+extern char column_type_alignment(Oid);
extern void printTableInit(printTableContent *const content,
const printTableOpt *opt, const char *title,
const int ncolumns, const int nrows);
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 1885bb1..6069024 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -90,6 +90,9 @@ typedef struct _psqlSettings
char *gfname; /* one-shot file output argument for \g */
char *gset_prefix; /* one-shot prefix argument for \gset */
+ bool crosstabview_output; /* one-shot request to print results in crosstab */
+ char *crosstabview_col_V; /* one-shot \crosstabview 1st argument */
+ char *crosstabview_col_H; /* one-shot \crosstabview 2nd argument */
bool notty; /* stdin or stdout is not a tty (as determined
* on startup) */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
