2015-12-13 8:14 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>
> 2015-12-10 19:29 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>>
>>
>>
>>> 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]
>>
>
> here is patch - supported syntax: \crosstabview VCol [+/-]HCol [HOrderCol]
>
> Order column should to contains any numeric value. Values are sorted on
> client side
>
fixed error messages
>
> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>>
>>> ┌──────────────────────────┬───────┬───────┬────────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┐
>>> │ customer │ led │ úno │ bře │ dub │ kvě │
>>> čen │ čec │ srp │ zář │ říj │ lis │
>>>
>>> ╞══════════════════════════╪═══════╪═══════╪════════╪═══════╪═══════╪═══════╪═══════╪═══════╪═══════╪═══════╪═══════╡
>>> │ A********** │ │ │ │ │
>>> │ │ │ │ │ 13000 │ │
>>> │ A******** │ │ │ 8000 │ │
>>> │ │ │ │ │ │ │
>>> │ B***** │ │ │ │ │
>>> │ │ │ │ │ │ 3200 │
>>> │ B*********************** │ │ │ │ │
>>> │ │ │ │ 26200 │ │ │
>>> │ B********* │ │ │ │ │
>>> │ │ 14000 │ │ │ │ │
>>> │ C********** │ │ │ │ 7740 │
>>> │ │ │ │ │ │ │
>>> │ C*** │ │ │ │ │
>>> │ │ │ │ 26000 │ │ │
>>> │ C***** │ │ │ │ 12000 │
>>> │ │ │ │ │ │ │
>>> │ G******* │ 30200 │ 26880 │ 13536 │ 39360 │ 60480 │
>>> 54240 │ 44160 │ 16320 │ 29760 │ 22560 │ 20160 │
>>> │ G*************** │ │ │ │ │ │
>>> 25500 │ │ │ │ │ │
>>> │ G********** │ │ │ │ │ │
>>> 16000 │ │ │ │ │ │
>>> │ I************* │ │ │ │ │
>>> │ │ │ 27920 │ │ │ │
>>> │ i**** │ │ │ │ 13500 │
>>> │ │ │ │ │ │ │
>>> │ n********* │ │ │ │ │
>>> │ │ 12600 │ │ │ │ │
>>> │ Q** │ │ │ │ │ 16700
>>> │ │ │ │ │ │ │
>>> │ S******* │ │ │ │ │
>>> │ │ 8000 │ │ │ │ │
>>> │ S******* │ │ │ │ │ 5368
>>> │ │ │ │ │ │ │
>>> │ s******* │ │ │ 5000 │ 3200 │
>>> │ │ │ │ │ │ │
>>>
>>> └──────────────────────────┴───────┴───────┴────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘
>>> (18 rows)
>>>
>>>
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>>
>>>>
>>>>
>>>>> 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
new file mode 100644
index 47e9da2..a45f4b8
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** lo_import 152801
*** 2461,2466 ****
--- 2461,2555 ----
</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
new file mode 100644
index f1336d5..9cb0c4a
*** a/src/bin/psql/Makefile
--- b/src/bin/psql/Makefile
*************** override CPPFLAGS := -I. -I$(srcdir) -I$
*** 23,29 ****
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 \
$(WIN32RES)
--- 23,29 ----
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 crosstabview.o \
$(WIN32RES)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index cf6876b..c743f57
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 46,51 ****
--- 46,52 ----
#include "mainloop.h"
#include "print.h"
#include "psqlscan.h"
+ #include "crosstabview.h"
#include "settings.h"
#include "variables.h"
*************** exec_command(const char *cmd,
*** 1081,1086 ****
--- 1082,1119 ----
free(pw2);
}
+ /* \crosstabview -- execute a query and display results in crosstab */
+ else if (strcmp(cmd, "crosstabview") == 0)
+ {
+ char *opt1,
+ *opt2,
+ *opt3;
+
+ opt1 = psql_scan_slash_option(scan_state,
+
OT_NORMAL, NULL, false);
+ opt2 = psql_scan_slash_option(scan_state,
+
OT_NORMAL, NULL, false);
+ opt3 = 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_col_O = opt3 ? pg_strdup(opt3) : 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
new file mode 100644
index a287eee..26cd233
*** a/src/bin/psql/common.c
--- b/src/bin/psql/common.c
***************
*** 24,29 ****
--- 24,30 ----
#include "command.h"
#include "copy.h"
#include "mbprint.h"
+ #include "crosstabview.h"
static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
*************** SendQuery(const char *query)
*** 1076,1085 ****
/* but printing results isn't: */
if (OK && results)
! OK = PrintQueryResults(results);
}
else
{
/* Fetch-in-segments mode */
OK = ExecQueryUsingCursor(query, &elapsed_msec);
ResetCancelConn();
--- 1077,1102 ----
/* but printing results isn't: */
if (OK && results)
! {
! if (pset.crosstabview_output)
! OK = PrintResultsInCrossTab(results,
!
pset.crosstabview_col_V,
!
pset.crosstabview_col_H,
!
pset.crosstabview_col_O);
! else
! OK = PrintQueryResults(results);
! }
}
else
{
+ if (pset.crosstabview_output)
+ {
+ psql_error("\\crosstabview cannot be executed since
active FETCH_COUNT = %d\n",
+ pset.fetch_count);
+ OK = false;
+ goto sendquery_cleanup;
+ }
+
/* Fetch-in-segments mode */
OK = ExecQueryUsingCursor(query, &elapsed_msec);
ResetCancelConn();
*************** sendquery_cleanup:
*** 1192,1197 ****
--- 1209,1231 ----
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;
+ }
+ if (pset.crosstabview_col_O)
+ {
+ free(pset.crosstabview_col_O);
+ pset.crosstabview_col_O = NULL;
+ }
+
return OK;
}
diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
new file mode 100644
index ...ac6c6f8
*** a/src/bin/psql/crosstabview.c
--- b/src/bin/psql/crosstabview.c
***************
*** 0 ****
--- 1,750 ----
+ /*
+ * 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 "catalog/pg_type.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, char* outer_rank, 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].outer_rank = outer_rank ?
strtod(outer_rank, NULL) : -1.0;
+ (*sorted_tab)[*count].rank = *count;
+ (*count)++;
+
+ qsort(*sorted_tab,
+ *count,
+ sizeof(struct pivot_field),
+ headerCompare);
+ }
+ }
+
+ /*
+ * Auxilary structure using for sorting only
+ */
+ struct p_rank
+ {
+ double outer_rank;
+ int old_pos;
+ };
+
+ static int
+ RankCompare(const void *a, const void *b)
+ {
+ return ((struct p_rank*)a)->outer_rank - ((struct
p_rank*)b)->outer_rank ;
+ }
+
+ static int
+ RankCompareDesc(const void *a, const void *b)
+ {
+ return ((struct p_rank*) b)->outer_rank - ((struct p_rank*)
a)->outer_rank;
+ }
+
+
+ /*
+ * Resort header by rank, ensure uniq outer ranks
+ */
+ static bool
+ sortColumnsByRank(struct pivot_field *columns, int nb_cols, int direction)
+ {
+ int i;
+ struct p_rank *p_ranks;
+ bool retval = true;
+
+ p_ranks = (struct p_rank *) pg_malloc(nb_cols * sizeof(struct p_rank));
+
+ for (i = 0; i < nb_cols; i++)
+ {
+ p_ranks[i].outer_rank = columns[i].outer_rank;
+ p_ranks[i].old_pos = i;
+ }
+
+ if (direction >= 0)
+ qsort(p_ranks,
+ nb_cols,
+ sizeof(struct p_rank),
+ RankCompare);
+ else
+ qsort(p_ranks,
+ nb_cols,
+ sizeof(struct p_rank),
+ RankCompareDesc);
+
+ for (i = 0; i < nb_cols; i++)
+ {
+ /* two adjecent outer sorted ranks should be different */
+ if (i > 0 && p_ranks[i].outer_rank == p_ranks[i - 1].outer_rank)
+ {
+ psql_error("Outer ranks are not unique.");
+ goto cleanup;
+ }
+
+ columns[p_ranks[i].old_pos].rank = i;
+ }
+
+ retval = true;
+
+ cleanup:
+
+ pg_free(p_ranks);
+
+ return retval;
+ }
+
+ /*
+ * 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,
+ int field_for_ranks)
+ {
+ 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
+ || src_col == field_for_ranks)
+ {
+ /*
+ 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,
+ const char* opt_field_for_rows,
/* COLV or null */
+ const char* opt_field_for_columns,
/* [-+]COLH or null */
+ const char* opt_field_for_ranks)
/* COLL 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 */
+ int field_for_ranks;
+
+ /* 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;
+ }
+
+ if (PQnfields(res) < 3 && opt_field_for_ranks != NULL)
+ {
+ psql_error(_("The query must return at least three 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;
+ }
+
+ if (opt_field_for_ranks)
+ {
+ Oid ftype;
+
+ field_for_ranks = indexOfColumn(opt_field_for_ranks, res);
+ if (field_for_ranks == field_for_columns || field_for_ranks ==
field_for_rows)
+ {
+ psql_error(_("The same column cannot be used for both
vertical and horizontal headers or ranks\n"));
+ goto error_return;
+ }
+
+ /* the rank column should be integer */
+ ftype = PQftype(res, field_for_ranks);
+ switch (ftype)
+ {
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ break;
+ default:
+ psql_error(_("The rank value should be numeric
value.\n"));
+ goto error_return;
+ }
+ }
+ else
+ field_for_ranks = -1;
+
+ /*
+ * 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),
+ NULL,
+ &num_rows,
+ &piv_rows,
+ rn);
+ }
+
+ if (!PQgetisnull(res, rn, field_for_columns))
+ {
+ if (field_for_ranks >= 0)
+ {
+ /* ensure not null values */
+ if (PQgetisnull(res, rn, field_for_ranks))
+ {
+ psql_error(_("The rank value should not
be null.\n"));
+ goto error_return;
+ }
+
+ accumHeader(PQgetvalue(res, rn,
field_for_columns),
+ PQgetvalue(res, rn,
field_for_ranks),
+ &num_columns,
+ &piv_columns,
+ rn);
+ }
+ else
+ accumHeader(PQgetvalue(res, rn,
field_for_columns),
+ NULL,
+ &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 client).
+ */
+ if (field_for_ranks >= 0 && !sortColumnsByRank(piv_columns,
+ num_columns,
+
columns_sort_direction))
+ goto error_return;
+
+ /*
+ * Second pass: sort the list of target columns (on the server).
+ */
+ else 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,
+ field_for_ranks);
+
+ 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 ...dd26322
*** a/src/bin/psql/crosstabview.h
--- b/src/bin/psql/crosstabview.h
***************
*** 0 ****
--- 1,36 ----
+ /*
+ * 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(), or explicitly
+ * from rank column
+ */
+ int rank;
+ double outer_rank;
+ };
+
+ /* prototypes */
+ extern bool
+ PrintResultsInCrossTab(PGresult* res,
+ const char* opt_field_for_rows,
+ const char* opt_field_for_columns,
+ const char* opt_field_for_ranks);
+
+ #endif /* CROSSTABVIEW_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
new file mode 100644
index 5b63e76..a893a64
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*************** slashUsage(unsigned short int pager)
*** 175,180 ****
--- 175,181 ----
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 [R]] 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
new file mode 100644
index 05d4b31..b2f8c2b
*** a/src/bin/psql/print.c
--- b/src/bin/psql/print.c
*************** printQuery(const PGresult *result, const
*** 3291,3320 ****
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);
}
/* set cells */
--- 3291,3299 ----
for (i = 0; i < cont.ncolumns; i++)
{
printTableAddHeader(&cont, PQfname(result, i),
! opt->translate_header,
!
column_type_alignment(PQftype(result, i)));
}
/* set cells */
*************** printQuery(const PGresult *result, const
*** 3356,3361 ****
--- 3335,3365 ----
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
new file mode 100644
index fd56598..218b185
*** a/src/bin/psql/print.h
--- b/src/bin/psql/print.h
*************** extern FILE *PageOutput(int lines, const
*** 175,181 ****
extern void ClosePager(FILE *pagerpipe);
extern void html_escaped_print(const char *in, FILE *fout);
!
extern void printTableInit(printTableContent *const content,
const printTableOpt *opt, const char *title,
const int ncolumns, const int nrows);
--- 175,181 ----
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
new file mode 100644
index 1885bb1..5993c1d
*** a/src/bin/psql/settings.h
--- b/src/bin/psql/settings.h
*************** typedef struct _psqlSettings
*** 90,95 ****
--- 90,99 ----
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 */
+ char *crosstabview_col_O; /* one-shot \crosstabview 3nd
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
new file mode 100644
index 8c48881..3337256
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*************** psql_completion(const char *text, int st
*** 936,943 ****
static const char *const backslash_commands[] = {
"\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
"\\copyright",
! "\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
! "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl",
"\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds",
"\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
--- 936,943 ----
static const char *const backslash_commands[] = {
"\\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",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds",
"\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
