2015-12-05 8:59 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2015-11-30 16:34 GMT+01:00 Daniel Verite <dan...@manitou-mail.org>: > >> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers