Hi,
I want to suggest a client-side \pivot command in psql, implemented
in the attached patch.
\pivot takes the current query in the buffer, execute it and
display it pivoted by interpreting the result as:
column1 => row in pivoted output
column2 => column in pivoted output
column3 => value at (row,column) in pivoted ouput
The advantage over the server-side crosstab() from contrib is in
ease of use, mostly because \pivot doesn't need in advance the
list of columns that result from pivoting.
Typical use cases are queries that produce values
along two axes:
SELECT a,b,aggr(something) FROM tbl GROUP a,b;
\pivot displays immediately the matrix-like representation
Or displaying "pairing" between columns, as in
SELECT a,b,'X' FROM tblA [LEFT|RIGHT|FULL] JOIN tblB...
which once pivoted shows in an easily readable way
what "a" is/isn't in relation with any "b".
Columns are sorted with strcmp(). I think a more adequate sort could
be obtained through a separate query with ORDER BY just for these
values (casted to their original type), but the patch doesn't do that
yet.
Also, \pivot could take optionally the query as an argument instead
of getting it only from the query buffer.
Anyway, does it look useful enough to be part of psql?
I guess I should push this to commitfest if that's the case.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index f1336d5..7e1ac24 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 pivot.o \
$(WIN32RES)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 6181a61..e348028 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 "pivot.h"
/*
* Editable database object types.
@@ -1083,6 +1084,12 @@ exec_command(const char *cmd,
free(pw2);
}
+ /* \pivot -- execute a query and show pivoted results */
+ else if (strcmp(cmd, "pivot") == 0)
+ {
+ success = doPivot(query_buf);
+ }
+
/* \prompt -- prompt and set variable */
else if (strcmp(cmd, "prompt") == 0)
{
diff --git a/src/bin/psql/pivot.c b/src/bin/psql/pivot.c
new file mode 100644
index 0000000..cb8ffdf
--- /dev/null
+++ b/src/bin/psql/pivot.c
@@ -0,0 +1,213 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2000-2015, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/pivot.c
+ */
+
+#include "common.h"
+#include "pqexpbuffer.h"
+#include "settings.h"
+#include "pivot.h"
+
+#include <string.h>
+
+static int
+headerCompare(const void *a, const void *b)
+{
+ return strcmp( ((struct pivot_field*)a)->name,
+ ((struct pivot_field*)b)->name);
+}
+
+static void
+accumHeader(char* name, int* count, struct pivot_field **sorted_tab, int row_number)
+{
+ struct pivot_field *p;
+
+ /*
+ * Search for name in sorted_tab. If it doesn't exist, insert it,
+ * otherwise do nothing.
+ */
+
+ if (*count >= 1)
+ {
+ p = (char**) 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
+printPivotedTuples(const PGresult *results,
+ int num_columns,
+ struct pivot_field *piv_columns,
+ int num_rows,
+ struct pivot_field *piv_rows)
+{
+ printQueryOpt popt = pset.popt;
+ printTableContent cont;
+ int i, j, k, rn;
+
+ popt.title = _("Pivoted query results");
+ printTableInit(&cont, &popt.topt, popt.title,
+ num_columns+1, num_rows);
+
+ /* Pivoting keeps the name of the first column */
+ printTableAddHeader(&cont, PQfname(results, 0),
+ popt.translate_header, 'l');
+
+ 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;
+ for (j = 0; j < num_columns; j++)
+ cont.cells[k*(num_columns+1)+j+1] = "";
+ }
+ cont.cellsadded = num_rows * (num_columns+1);
+
+ /* Set all the "inside" cells */
+ 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, 0))
+ {
+ row_name = PQgetvalue(results, rn, 0);
+ 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, 1))
+ {
+ col_name = PQgetvalue(results, rn, 1);
+ 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)
+ {
+ cont.cells[1+col_number+row_number*(num_columns+1)] =
+ (!PQgetisnull(results, rn, 2)) ? PQgetvalue(results, rn, 2) :
+ (popt.nullPrint ? popt.nullPrint : "");
+ }
+ }
+
+ printTable(&cont, pset.queryFout, pset.logfile);
+ printTableCleanup(&cont);
+}
+
+/*
+ * doPivot -- handler for \pivot
+ *
+ */
+bool
+doPivot(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;
+
+ if (!query_buf || query_buf->len <= 0)
+ {
+ psql_error(_("\\pivot cannot be used with an empty query\n"));
+ return false;
+ }
+
+ 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) != 3)
+ {
+ psql_error(_("A query to pivot must return 3 columns\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, 0))
+ {
+ accumHeader(PQgetvalue(res, rn, 0), &num_rows, &piv_rows, rn);
+ }
+
+ if (!PQgetisnull(res, rn, 1))
+ {
+ accumHeader(PQgetvalue(res, rn, 1), &num_columns, &piv_columns, rn);
+ }
+ }
+
+ /*
+ * Second pass: print the pivoted results
+ */
+ printPivotedTuples(res,
+ num_columns,
+ piv_columns,
+ num_rows,
+ piv_rows);
+ }
+
+ }
+
+ pg_free(piv_columns);
+ pg_free(piv_rows);
+
+ PQclear(res);
+
+ return OK;
+}
+
diff --git a/src/bin/psql/pivot.h b/src/bin/psql/pivot.h
new file mode 100644
index 0000000..7db2d7e
--- /dev/null
+++ b/src/bin/psql/pivot.h
@@ -0,0 +1,26 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2000-2015, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/pivot.h
+ */
+
+#ifndef PIVOT_H
+#define PIVOT_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 doPivot(PQExpBuffer query_buf);
+
+#endif /* PIVOT_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
