Pavel Stehule wrote:
> postgres=# \crosstabview 4 +month label
>
> Maybe using optional int order column instead label is better - then you can
> do sort on client side
>
> so the syntax can be "\crosstabview VCol [+/-]HCol [[+-]HOrderCol]
In the meantime I've followed a different idea: allowing the
vertical header to be sorted too, still server-side.
That's because to me, the first impulse for a user noticing that
it's not sorted vertically would be to write
\crosstabview +customer month
rather than figure out the
\crosstabview customer +month_number month_name
invocation.
But both ways aren't even mutually exclusive. We could support
\crosstabview [+|-]colV[:labelV] [+|-]colH[:labelH]
it's more complicated to understand, but not harder to implement.
Also, a non-zero FETCH_COUNT is supported by this version of the patch,
if the first internal FETCH retrieves less than FETCH_COUNT rows.
Otherwise a specific error is emitted.
Also there are minor changes in arguments and callers following
recent code changes for \o
Trying to crosstab with 10k+ distinct values vertically, I've noticed
that the current code is too slow, spending too much time
sorting. I'm currently replacing its simple arrays of distinct values
with AVL binary trees, which I expect to be much more efficient for
this.
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 e4f72a8..2a998b2 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 6e8c623..6ba5efa 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 a287eee..d8c0f70 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -24,6 +24,7 @@
#include "command.h"
#include "copy.h"
#include "mbprint.h"
+#include "crosstabview.h"
static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
@@ -906,6 +907,8 @@ PrintQueryResults(PGresult *results)
/* store or print the data ... */
if (pset.gset_prefix)
success = StoreQueryTuple(results);
+ else if (pset.crosstabview_output)
+ success = PrintResultsInCrossTab(results);
else
success = PrintQueryTuples(results);
/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1076,7 +1079,9 @@ SendQuery(const char *query)
/* but printing results isn't: */
if (OK && results)
+ {
OK = PrintQueryResults(results);
+ }
}
else
{
@@ -1192,6 +1197,18 @@ sendquery_cleanup:
pset.gset_prefix = NULL;
}
+ /* reset \crosstabview settings */
+ 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;
}
@@ -1354,7 +1371,25 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
is_pager = true;
}
- printQuery(results, &my_popt, fout, is_pager, pset.logfile);
+ if (pset.crosstabview_output)
+ {
+ if (ntuples < fetch_count)
+ PrintResultsInCrossTab(results);
+ else
+ {
+ /*
+ crosstabview is denied if the whole set of rows is not
+ guaranteed to be fetched in the first iteration, because
+ it's expected in memory as a single PGresult structure.
+ */
+ psql_error("\\crosstabview must be used with less than FETCH_COUNT (%d) rows\n",
+ fetch_count);
+ PQclear(results);
+ break;
+ }
+ }
+ else
+ printQuery(results, &my_popt, fout, is_pager, pset.logfile);
PQclear(results);
diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
new file mode 100644
index 0000000..6d13b57
--- /dev/null
+++ b/src/bin/psql/crosstabview.c
@@ -0,0 +1,644 @@
+/*
+ * 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, false, 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)
+{
+ /* [-|+]COLV or null */
+ const char* opt_field_for_rows = pset.crosstabview_col_V;
+ /* [-|+]COLH or null */
+ const char* opt_field_for_columns = pset.crosstabview_col_H;
+ 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 */
+ int rows_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 in crosstab */
+ if (opt_field_for_rows == NULL)
+ field_for_rows = 0;
+ else
+ {
+ if (*opt_field_for_rows == '-')
+ {
+ rows_sort_direction = -1;
+ opt_field_for_rows++;
+ }
+ else if (*opt_field_for_rows == '+')
+ {
+ rows_sort_direction = 1;
+ opt_field_for_rows++;
+ }
+ field_for_rows = indexOfColumn(opt_field_for_rows, res);
+ if (field_for_rows < 0)
+ goto error_return;
+ }
+
+ if (field_for_rows < 0)
+ goto error_return;
+
+ /* field for columns in crosstab */
+ 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 part: 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 part: sort the list of target columns on the server.
+ */
+ if (columns_sort_direction != 0)
+ {
+ if (!sortColumns(PQftype(res, field_for_columns),
+ piv_columns,
+ num_columns,
+ columns_sort_direction))
+ goto error_return;
+ }
+
+
+ /*
+ * Third part: sort the list of target columns on the server.
+ */
+ if (rows_sort_direction != 0)
+ {
+ if (!sortColumns(PQftype(res, field_for_rows),
+ piv_rows,
+ num_rows,
+ rows_sort_direction))
+ goto error_return;
+ }
+
+ /*
+ * Fourth part: 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..e8a0e01
--- /dev/null
+++ b/src/bin/psql/crosstabview.h
@@ -0,0 +1,30 @@
+/*
+ * 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);
+#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 05d4b31..b2f8c2b 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -3291,30 +3291,9 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
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 */
@@ -3356,6 +3335,31 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
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 fd565984..218b185 100644
--- a/src/bin/psql/print.h
+++ b/src/bin/psql/print.h
@@ -175,7 +175,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) */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b58ec14..07ba26c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -935,7 +935,8 @@ psql_completion(const char *text, int start, int end)
};
static const char *const backslash_commands[] = {
- "\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright",
+ "\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
+ "\\copyright", "\\crosstabview",
"\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
