diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile index f1336d5..92f9f44 100644 --- a/src/bin/psql/Makefile +++ b/src/bin/psql/Makefile @@ -23,7 +23,7 @@ override CPPFLAGS := -I. -I$(srcdir) -I$(libpq_srcdir) -I$(top_srcdir)/src/bin/p OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \ startup.o prompt.o variables.o large_obj.o print.o describe.o \ tab-complete.o mbprint.o dumputils.o keywords.o kwlookup.o \ - sql_help.o \ + sql_help.o rotate.o \ $(WIN32RES) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 6181a61..d6c440a 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -48,6 +48,7 @@ #include "psqlscan.h" #include "settings.h" #include "variables.h" +#include "rotate.h" /* * Editable database object types. @@ -1083,6 +1084,23 @@ exec_command(const char *cmd, free(pw2); } + /* \rotate -- execute a query and show results rotated along axis */ + else if (strcmp(cmd, "rotate") == 0) + { + char *opt1, + *opt2; + + opt1 = psql_scan_slash_option(scan_state, + OT_NORMAL, NULL, true); + opt2 = psql_scan_slash_option(scan_state, + OT_NORMAL, NULL, true); + + success = doRotate(opt1, opt2, query_buf); + + free(opt1); + free(opt2); + } + /* \prompt -- prompt and set variable */ else if (strcmp(cmd, "prompt") == 0) { diff --git a/src/bin/psql/rotate.c b/src/bin/psql/rotate.c new file mode 100644 index 0000000..094ea03 --- /dev/null +++ b/src/bin/psql/rotate.c @@ -0,0 +1,352 @@ +/* + * psql - the PostgreSQL interactive terminal + * + * Copyright (c) 2000-2015, PostgreSQL Global Development Group + * + * src/bin/psql/rotate.c + */ + +#include "common.h" +#include "pqexpbuffer.h" +#include "settings.h" +#include "rotate.h" + +#include + +static int +headerCompare(const void *a, const void *b) +{ + return strcmp( ((struct pivot_field*)a)->name, + ((struct pivot_field*)b)->name); +} + +static void +accumHeader(char* name, int* count, struct pivot_field **sorted_tab, int row_number) +{ + struct pivot_field *p; + + /* + * Search for name in sorted_tab. If it doesn't exist, insert it, + * otherwise do nothing. + */ + + if (*count >= 1) + { + p = (struct pivot_field*) bsearch(&name, + *sorted_tab, + *count, + sizeof(struct pivot_field), + headerCompare); + } + else + p=NULL; + + if (!p) + { + *sorted_tab = pg_realloc(*sorted_tab, sizeof(struct pivot_field) * (1+*count)); + (*sorted_tab)[*count].name = name; + (*sorted_tab)[*count].rank = *count; + (*count)++; + + qsort(*sorted_tab, + *count, + sizeof(struct pivot_field), + headerCompare); + } +} + +static void +printRotation(const PGresult *results, + int num_columns, + struct pivot_field *piv_columns, + int field_for_columns, + int num_rows, + struct pivot_field *piv_rows, + int field_for_rows) +{ + printQueryOpt popt = pset.popt; + printTableContent cont; + int i, j, k, rn; + char** allocated_cells; /* Pointers for cell contents that are allocated + * in this function, when cells cannot simply point to + * PQgetvalue(results, ...) */ + + popt.title = _("Rotated query results"); + printTableInit(&cont, &popt.topt, popt.title, + num_columns+1, num_rows); + + /* The name of the first column is kept unchanged by the rotation */ + printTableAddHeader(&cont, PQfname(results, 0), + popt.translate_header, 'l'); + + /* The names of the next columns come from piv_columns[] */ + for (i = 0; i < num_columns; i++) + { + printTableAddHeader(&cont, piv_columns[i].name, + popt.translate_header, 'l'); + } + + /* Set row names in the first output column */ + for (i = 0; i < num_rows; i++) + { + 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*)); + + /* 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 - piv_columns); + } + + /* Place value into cell */ + if (col_number>=0 && row_number>=0) + { + int idx = 1 + col_number + row_number*(num_columns+1); + int src_col = 0; /* column number in source result */ + int k = 0; + + do { + char *content; + + if (PQnfields(results) == 2) + { + /* + special case: when the source has only 2 columns, use a + X (cross/checkmark) for the cell content, and set + src_col to a virtual additional column. + */ + content = "X"; + src_col = 3; + } + else if (src_col == field_for_rows || src_col == field_for_columns) + { + /* + The source values that produce headers are not processed + in this loop, only the values that end up inside the grid. + */ + src_col++; + continue; + } + else + { + content = (!PQgetisnull(results, rn, src_col)) ? + PQgetvalue(results, rn, src_col) : + (popt.nullPrint ? popt.nullPrint : ""); + } + + if (cont.cells[idx] != NULL && cont.cells[idx][0] != '\0') + { + /* + * Multiple values for the same (row,col) are projected + * into the same cell. When this happens, separate the + * previous content of the cell from the new value by a + * newline. + */ + int content_size = + strlen(cont.cells[idx]) + + 2 /* room for [CR],LF or space */ + + strlen(content) + + 1; /* '\0' */ + char *new_content; + + /* + * idx2 is an index into allocated_cells. It differs from + * idx (index into cont.cells), because vertical and + * horizontal headers are included in `cont.cells` but + * excluded from allocated_cells. + */ + int idx2 = (row_number * num_columns) + col_number; + + if (allocated_cells[idx2] != NULL) + { + new_content = pg_realloc(allocated_cells[idx2], content_size); + } + else + { + /* + * At this point, cont.cells[idx] still contains a + * PQgetvalue() pointer. Just after, it will contain + * a new pointer maintained in allocated_cells[], and + * freed at the end of this function. + */ + new_content = pg_malloc(content_size); + strcpy(new_content, cont.cells[idx]); + } + cont.cells[idx] = new_content; + allocated_cells[idx2] = new_content; + + /* + * Contents that are on adjacent columns in the source results get + * separated by one space in the target. + * Contents that are on different rows in the source get + * separated by newlines in the target. + */ + if (k==0) + strcat(new_content, "\n"); + else + strcat(new_content, " "); + strcat(new_content, content); + } + else + { + cont.cells[idx] = content; + } + k++; + src_col++; + } while (src_col < PQnfields(results)); + } + } + + printTable(&cont, pset.queryFout, pset.logfile); + printTableCleanup(&cont); + + + for (i=0; i < num_rows * num_columns; i++) + { + if (allocated_cells[i] != NULL) + pg_free(allocated_cells[i]); + } + + pg_free(allocated_cells); +} + +/* + * doRotate -- handler for \rotate + * + */ +bool +doRotate(const char* opt_field_for_rows, + const char* opt_field_for_columns, + PQExpBuffer query_buf) +{ + PGresult *res; + int rn; + struct pivot_field *piv_columns = NULL; + struct pivot_field *piv_rows = NULL; + int num_columns = 0; + int num_rows = 0; + bool OK = true; + + /* 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 (!query_buf || query_buf->len <= 0) + { + psql_error(_("\\rotate cannot be used with an empty query\n")); + return false; + } + + if (!opt_field_for_rows) + field_for_rows = 0; + else + { + field_for_rows = atoi(opt_field_for_rows)-1; + } + + if (!opt_field_for_columns) + field_for_columns = 1; + else + { + field_for_columns = atoi(opt_field_for_columns)-1; + } + + + res = PSQLexec(query_buf->data); + + if (!res) + return false; /* error processing has been done by PSQLexec() */ + + if (PQresultStatus(res) == PGRES_TUPLES_OK) + { + if (PQnfields(res) < 2) + { + psql_error(_("A query must return at least two columns to rotate its output\n")); + OK = false; + } + else if (field_for_rows < 0 || field_for_rows >= PQnfields(res) || + field_for_columns < 0 || field_for_columns >= PQnfields(res) ) + { + psql_error(_("Invalid column number\n")); + OK = false; + } + else + { + /* + * First pass: accumulate row names and column names, each into their + * sorted array + */ + for (rn = 0; rn < PQntuples(res); rn++) + { + if (!PQgetisnull(res, rn, field_for_rows)) + { + accumHeader(PQgetvalue(res, rn, field_for_rows), &num_rows, &piv_rows, rn); + } + + if (!PQgetisnull(res, rn, field_for_columns)) + { + accumHeader(PQgetvalue(res, rn, field_for_columns), &num_columns, &piv_columns, rn); + } + } + + /* + * Second pass: print the rotated results + */ + printRotation(res, + num_columns, + piv_columns, + field_for_columns, + num_rows, + piv_rows, + field_for_rows); + } + + } + + pg_free(piv_columns); + pg_free(piv_rows); + + PQclear(res); + + return OK; +} + diff --git a/src/bin/psql/rotate.h b/src/bin/psql/rotate.h new file mode 100644 index 0000000..8fed224 --- /dev/null +++ b/src/bin/psql/rotate.h @@ -0,0 +1,28 @@ +/* + * psql - the PostgreSQL interactive terminal + * + * Copyright (c) 2000-2015, PostgreSQL Global Development Group + * + * src/bin/psql/rotate.h + */ + +#ifndef ROTATE_H +#define ROTATE_H + +struct pivot_field +{ + /* pointer obtained by PGgetvalue() in column 0 or 1 */ + char* name; + + /* rank of that field in the list of fields, starting at 0. + rank=N means it's the Nth distinct field encountered when looping + through rows in their initial order */ + int rank; +}; + +/* prototypes */ +extern bool doRotate(const char* opt_field_for_rows, + const char* opt_field_for_columns, + PQExpBuffer query_buf); + +#endif /* ROTATE_H */