2015-12-05 8:59 GMT+01:00 Pavel Stehule <[email protected]>:
>
>
> 2015-11-30 16:34 GMT+01:00 Daniel Verite <[email protected]>:
>
>> Pavel Stehule wrote:
>>
>> > [ \rotate being a wrong name ]
>>
>> Here's an updated patch.
>>
>
> Today I have a time to play with it. I am sorry for delay.
>
>
>>
>> First it renames the command to \crosstabview, which hopefully may
>> be more consensual, at least it's semantically much closer to crosstab .
>>
>
> Thank you very much - it is good name.
>
>
>>
>> > 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.
>>
>>
> .Usually you have not natural order for both dimensions - I miss a
> possibility to set [+/-] order for vertical dimension
>
> For my query
>
> select sum(amount) as amount, to_char(date_trunc('month', closed),'TMmon')
> as Month, customer
> from data group by customer, to_char(date_trunc('month', closed),
> 'TMmon'), extract(month from closed)
> order by extract(month from closed);
>
> I cannot to push order by customer - and I have to use
>
>
> select sum(amount) as amount, extract(month from closed) as Month,
> customer from data group by customer, extract(month from closed) order by
> customer;
>
> and \crosstabview 3 +2
>
> So possibility to enforce order for vertical dimension and use data order
> for horizontal dimension can be really useful. Other way using special
> column for sorting
>
> some like \crosstabview verticalcolumn horizontalcolumn
> sorthorizontalcolumn
>
>
> Next - I use "fetch_count" > 0. Your new version work only with
> "fetch_cunt <= 0". It is limit - but I am thinking it is acceptable.In this
> case some warning should be displayed - some like "crosstabview doesn't
> work with FETCH_COUNT > 0"
>
> I miss support for autocomplete and \?
>
>
> Regards
>
> Pavel
>
>
I did few minor changes in your patch
1. autocomplete + warning on active FETCH_COUNT (the worning should be
replaced by error, the statement show nothing)
2. support for labels
postgres=# \d data
Table "public.data"
┌──────────┬─────────┬───────────┐
│ Column │ Type │ Modifiers │
╞══════════╪═════════╪═══════════╡
│ id │ integer │ │
│ customer │ text │ │
│ name │ text │ │
│ amount │ integer │ │
│ expected │ text │ │
│ closed │ date │ │
└──────────┴─────────┴───────────┘
postgres=# select sum(amount) as amount, extract(month from closed) as
Month, to_char(date_trunc('month', closed), 'TMmon') as label, customer
from data group by customer, to_char(date_trunc('month', closed), 'TMmon'),
extract(month from closed) order by customer;
postgres=# \crosstabview 4 +month label
┌──────────────────────────┬───────┬───────┬────────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┐
│ 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 e4f72a8..ed802b3
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** lo_import 152801
*** 2449,2454 ****
--- 2449,2543 ----
</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 8eca4cf..8305d1e
*** 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_L = 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 3254a14..8a7984d
*** 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"
*************** SendQuery(const char *query)
*** 1062,1071 ****
/* but printing results isn't: */
if (OK && results)
! OK = PrintQueryResults(results);
}
else
{
/* Fetch-in-segments mode */
OK = ExecQueryUsingCursor(query, &elapsed_msec);
ResetCancelConn();
--- 1063,1084 ----
/* 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_L);
! else
! OK = PrintQueryResults(results);
! }
}
else
{
+ if (pset.crosstabview_output)
+ psql_error("\\crosstabview is ignored due active
FETCH_COUNT = %d\n",
+ pset.fetch_count);
+
/* Fetch-in-segments mode */
OK = ExecQueryUsingCursor(query, &elapsed_msec);
ResetCancelConn();
*************** sendquery_cleanup:
*** 1178,1183 ****
--- 1191,1213 ----
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_L)
+ {
+ 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 ...6dce5a4
*** a/src/bin/psql/crosstabview.c
--- b/src/bin/psql/crosstabview.c
***************
*** 0 ****
--- 1,636 ----
+ /*
+ * 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, char *label, 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].label = label;
+ (*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,
+ int field_for_labels)
+ {
+ 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,
+ field_for_labels < 0 ?
+
piv_columns[horiz_map[i]].name :
+
piv_columns[horiz_map[i]].label,
+ 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_labels)
+ {
+ /*
+ 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_labels)
/* 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_labels;
+
+ /* 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_labels != 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_labels)
+ {
+ field_for_labels = indexOfColumn(opt_field_for_labels, res);
+ if (field_for_labels == field_for_columns || field_for_labels
== field_for_rows)
+ {
+ psql_error(_("The same column cannot be used for both
vertical and horizontal headers or label\n"));
+ goto error_return;
+ }
+ }
+ else
+ field_for_labels = -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))
+ {
+ accumHeader(PQgetvalue(res, rn, field_for_columns),
+ field_for_labels >= 0 ? PQgetvalue(res, rn,
field_for_labels) : 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 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,
+ field_for_labels);
+
+ 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 ...58cd33b
*** a/src/bin/psql/crosstabview.h
--- b/src/bin/psql/crosstabview.h
***************
*** 0 ****
--- 1,35 ----
+ /*
+ * 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;
+ char* label;
+
+ /* 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,
+ const char* opt_field_for_labels);
+
+ #endif /* CROSSTABVIEW_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
new file mode 100644
index 5b63e76..e4a9ddf
*** 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 L] 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 190f2bc..4e4b5e7
*** a/src/bin/psql/print.c
--- b/src/bin/psql/print.c
*************** printQuery(const PGresult *result, const
*** 3239,3268 ****
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 */
--- 3239,3247 ----
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
*** 3304,3309 ****
--- 3283,3313 ----
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 df514cf..409f3e5
*** a/src/bin/psql/print.h
--- b/src/bin/psql/print.h
*************** extern FILE *PageOutput(int lines, const
*** 171,177 ****
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);
--- 171,177 ----
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..429d6fb
*** 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_L; /* 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 b58ec14..0f79e0a
*** 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
