Support \crosstabview in psql

\crosstabview is a completely different way to display results from a
query: instead of a vertical display of rows, the data values are placed
in a grid where the column and row headers come from the data itself,
similar to a spreadsheet.

The sort order of the horizontal header can be specified by using
another column in the query, and the vertical header determines its
ordering from the order in which they appear in the query.

This only allows displaying a single value in each cell.  If more than
one value correspond to the same cell, an error is thrown.  Merging of
values can be done in the query itself, if necessary.  This may be
revisited in the future.

Author: Daniel Verité
Reviewed-by: Pavel Stehule, Dean Rasheed

2 files changed, 294 insertions, 0 deletions

diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index edcc414630c..9e98e9d62fd 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2476,6 +2476,7 @@ execute q;
 +------------------+-------------------+
 
 deallocate q;
+\pset linestyle ascii
 prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n;
 \pset format asciidoc
 \pset expanded off
@@ -2682,6 +2683,9 @@ execute q;
 <l|int >l|11
 |====
 deallocate q;
+\pset format aligned
+\pset expanded off
+\pset border 1
 -- SHOW_CONTEXT
 \set SHOW_CONTEXT never
 do $$
@@ -2710,3 +2714,188 @@ NOTICE:  foo
 CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE
 ERROR:  bar
 CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE
+--
+-- \crosstabview
+--
+CREATE TABLE ctv_data (v, h, c, i, d) AS
+VALUES
+   ('v1','h2','foo', 3, '2015-04-01'::date),
+   ('v2','h1','bar', 3, '2015-01-02'),
+   ('v1','h0','baz', NULL, '2015-07-12'),
+   ('v0','h4','qux', 4, '2015-07-15'),
+   ('v0','h4','dbl', -3, '2014-12-15'),
+   ('v0',NULL,'qux', 5, '2014-07-15'),
+   ('v1','h2','quux',7, '2015-04-04');
+-- running \crosstabview after query uses query in buffer
+SELECT v, EXTRACT(year FROM d), count(*)
+ FROM ctv_data
+ GROUP BY 1, 2
+ ORDER BY 1, 2;
+ v  | date_part | count 
+----+-----------+-------
+ v0 |      2014 |     2
+ v0 |      2015 |     1
+ v1 |      2015 |     3
+ v2 |      2015 |     1
+(4 rows)
+
+-- basic usage with 3 columns
+ \crosstabview
+ v  | 2014 | 2015 
+----+------+------
+ v0 |    2 |    1
+ v1 |      |    3
+ v2 |      |    1
+(3 rows)
+
+-- ordered months in horizontal header, quoted column name
+SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
+ count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
+ \crosstabview v "month name":num 4
+ v  | Jan | Apr | Jul | Dec 
+----+-----+-----+-----+-----
+ v0 |     |     |   2 |   1
+ v1 |     |   2 |   1 |    
+ v2 |   1 |     |     |    
+(3 rows)
+
+-- ordered months in vertical header, ordered years in horizontal header
+SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
+  EXTRACT(month FROM d) AS month,
+  format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
+  FROM ctv_data
+  GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
+ORDER BY month
+\crosstabview "month name" year:year format
+ month name |      2014       |      2015      
+------------+-----------------+----------------
+ Jan        |                 | sum=3 avg=3.0
+ Apr        |                 | sum=10 avg=5.0
+ Jul        | sum=5 avg=5.0   | sum=4 avg=4.0
+ Dec        | sum=-3 avg=-3.0 | 
+(4 rows)
+
+-- combine contents vertically into the same cell (V/H duplicates)
+SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
+ \crosstabview 1 2 3
+ v  | h4  |     | h0  |  h2  | h1  
+----+-----+-----+-----+------+-----
+ v0 | qux+| qux |     |      | 
+    | dbl |     |     |      | 
+ v1 |     |     | baz | foo +| 
+    |     |     |     | quux | 
+ v2 |     |     |     |      | bar
+(3 rows)
+
+-- horizontal ASC order from window function
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+ v  | h0  | h1  |  h2  | h4  |     
+----+-----+-----+------+-----+-----
+ v0 |     |     |      | qux+| qux
+    |     |     |      | dbl | 
+ v1 | baz |     | foo +|     | 
+    |     |     | quux |     | 
+ v2 |     | bar |      |     | 
+(3 rows)
+
+-- horizontal DESC order from window function
+SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+ v  |     | h4  |  h2  | h1  | h0  
+----+-----+-----+------+-----+-----
+ v0 | qux | qux+|      |     | 
+    |     | dbl |      |     | 
+ v1 |     |     | foo +|     | baz
+    |     |     | quux |     | 
+ v2 |     |     |      | bar | 
+(3 rows)
+
+-- horizontal ASC order from window function, NULLs pushed rightmost
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+ v  | h0  | h1  |  h2  | h4  |     
+----+-----+-----+------+-----+-----
+ v0 |     |     |      | qux+| qux
+    |     |     |      | dbl | 
+ v1 | baz |     | foo +|     | 
+    |     |     | quux |     | 
+ v2 |     | bar |      |     | 
+(3 rows)
+
+-- only null, no column name, 2 columns: error
+SELECT null,null \crosstabview
+The query must return at least two columns to be shown in crosstab
+-- only null, no column name, 3 columns: works
+SELECT null,null,null \crosstabview
+ ?column? |  
+----------+--
+          | 
+(1 row)
+
+-- null display
+\pset null '#null#'
+SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
+GROUP BY v, h ORDER BY h,v
+ \crosstabview v h i
+ v  |   h0   | h1 | h2 | h4 | #null# 
+----+--------+----+----+----+--------
+ v1 | #null# |    | 3 +|    | 
+    |        |    | 7  |    | 
+ v2 |        | 3  |    |    | 
+ v0 |        |    |    | 4 +| 5
+    |        |    |    | -3 | 
+(3 rows)
+
+\pset null ''
+-- refer to columns by position
+SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 2 1 4
+ h  |  v1  | v2  | v0  
+----+------+-----+-----
+ h0 | baz  |     | 
+ h1 |      | bar | 
+ h2 | foo +|     | 
+    | quux |     | 
+ h4 |      |     | qux+
+    |      |     | dbl
+    |      |     | qux
+(5 rows)
+
+-- refer to columns by positions and names mixed
+SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 1 "h" 4
+ v  | h0  | h1  |  h2  | h4  |     
+----+-----+-----+------+-----+-----
+ v1 | baz |     | foo +|     | 
+    |     |     | quux |     | 
+ v2 |     | bar |      |     | 
+ v0 |     |     |      | qux+| qux
+    |     |     |      | dbl | 
+(3 rows)
+
+-- error: bad column name
+SELECT v,h,c,i FROM ctv_data
+ \crosstabview v h j
+Invalid column name: j
+-- error: bad column number
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 1 5
+Invalid column number: 5
+-- error: same H and V columns
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 h 4
+The same column cannot be used for both vertical and horizontal headers
+-- error: too many columns
+SELECT a,a,1 FROM generate_series(1,3000) AS a
+ \crosstabview
+Maximum number of columns (1600) exceeded
+-- error: only one column
+SELECT 1 \crosstabview
+The query must return at least two columns to be shown in crosstab
+DROP TABLE ctv_data;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index c5b36649491..2e332afc963 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -326,6 +326,8 @@ execute q;
 
 deallocate q;
 
+\pset linestyle ascii
+
 prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n;
 
 \pset format asciidoc
@@ -351,6 +353,10 @@ execute q;
 
 deallocate q;
 
+\pset format aligned
+\pset expanded off
+\pset border 1
+
 -- SHOW_CONTEXT
 
 \set SHOW_CONTEXT never
@@ -373,3 +379,102 @@ begin
   raise notice 'foo';
   raise exception 'bar';
 end $$;
+
+--
+-- \crosstabview
+--
+
+CREATE TABLE ctv_data (v, h, c, i, d) AS
+VALUES
+   ('v1','h2','foo', 3, '2015-04-01'::date),
+   ('v2','h1','bar', 3, '2015-01-02'),
+   ('v1','h0','baz', NULL, '2015-07-12'),
+   ('v0','h4','qux', 4, '2015-07-15'),
+   ('v0','h4','dbl', -3, '2014-12-15'),
+   ('v0',NULL,'qux', 5, '2014-07-15'),
+   ('v1','h2','quux',7, '2015-04-04');
+
+-- running \crosstabview after query uses query in buffer
+SELECT v, EXTRACT(year FROM d), count(*)
+ FROM ctv_data
+ GROUP BY 1, 2
+ ORDER BY 1, 2;
+-- basic usage with 3 columns
+ \crosstabview
+
+-- ordered months in horizontal header, quoted column name
+SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
+ count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
+ \crosstabview v "month name":num 4
+
+-- ordered months in vertical header, ordered years in horizontal header
+SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
+  EXTRACT(month FROM d) AS month,
+  format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
+  FROM ctv_data
+  GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
+ORDER BY month
+\crosstabview "month name" year:year format
+
+-- combine contents vertically into the same cell (V/H duplicates)
+SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
+ \crosstabview 1 2 3
+
+-- horizontal ASC order from window function
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+
+-- horizontal DESC order from window function
+SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+
+-- horizontal ASC order from window function, NULLs pushed rightmost
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+
+-- only null, no column name, 2 columns: error
+SELECT null,null \crosstabview
+
+-- only null, no column name, 3 columns: works
+SELECT null,null,null \crosstabview
+
+-- null display
+\pset null '#null#'
+SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
+GROUP BY v, h ORDER BY h,v
+ \crosstabview v h i
+\pset null ''
+
+-- refer to columns by position
+SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 2 1 4
+
+-- refer to columns by positions and names mixed
+SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 1 "h" 4
+
+-- error: bad column name
+SELECT v,h,c,i FROM ctv_data
+ \crosstabview v h j
+
+-- error: bad column number
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 1 5
+
+-- error: same H and V columns
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 h 4
+
+-- error: too many columns
+SELECT a,a,1 FROM generate_series(1,3000) AS a
+ \crosstabview
+
+-- error: only one column
+SELECT 1 \crosstabview
+
+DROP TABLE ctv_data;