1 files changed, 216 insertions, 0 deletions
diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out
new file mode 100644
index 0000000..e09e331
--- /dev/null
+++ b/src/test/regress/expected/psql_crosstab.out
@@ -0,0 +1,216 @@
+--
+-- \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');
+-- make plans more stable
+ANALYZE ctv_data;
+-- 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  | extract | 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" 4 num
+ 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 format year
+ "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 c r
+ 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 c r
+ 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 c r
+ 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
+\crosstabview: query must return at least three columns
+-- 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)
+
+-- refer to columns by quoted names, check downcasing of unquoted name
+SELECT 1 as "22", 2 as b, 3 as "Foo"
+ \crosstabview "22" B "Foo"
+ 22 | 2 
+----+---
+  1 | 3
+(1 row)
+
+-- error: bad column name
+SELECT v,h,c,i FROM ctv_data
+ \crosstabview v h j
+\crosstabview: column name not found: "j"
+-- error: need to quote name
+SELECT 1 as "22", 2 as b, 3 as "Foo"
+ \crosstabview 1 2 Foo
+\crosstabview: column name not found: "foo"
+-- error: need to not quote name
+SELECT 1 as "22", 2 as b, 3 as "Foo"
+ \crosstabview 1 "B" "Foo"
+\crosstabview: column name not found: "B"
+-- error: bad column number
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 1 5
+\crosstabview: column number 5 is out of range 1..4
+-- error: same H and V columns
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 h 4
+\crosstabview: vertical and horizontal headers must be different columns
+-- error: too many columns
+SELECT a,a,1 FROM generate_series(1,3000) AS a
+ \crosstabview
+\crosstabview: maximum number of columns (1600) exceeded
+-- error: only one column
+SELECT 1 \crosstabview
+\crosstabview: query must return at least three columns
+DROP TABLE ctv_data;
+-- check error reporting (bug #14476)
+CREATE TABLE ctv_data (x int, y int, v text);
+INSERT INTO ctv_data SELECT 1, x, '*' || x FROM generate_series(1,10) x;
+SELECT * FROM ctv_data \crosstabview
+ x | 1  | 2  | 3  | 4  | 5  | 6  | 7  | 8  | 9  | 10  
+---+----+----+----+----+----+----+----+----+----+-----
+ 1 | *1 | *2 | *3 | *4 | *5 | *6 | *7 | *8 | *9 | *10
+(1 row)
+
+INSERT INTO ctv_data VALUES (1, 10, '*'); -- duplicate data to cause error
+SELECT * FROM ctv_data \crosstabview
+\crosstabview: query result contains multiple data values for row "1", column "10"
+DROP TABLE ctv_data;