Alvaro Herrera wrote:

> I messed with that code some more, as it looked unnecessarily
> complicated; please see attached and verify that it still behaves
> sanely.  This needs those regression tests you promised.  I tested a few
> cases and it seems good to me.

I've fixed a couple things over v16:
- avoid passing every cell through psprintf, which happened due
  to cont.cells being pre-initialized to empty strings.
- adjusted the loop freeing allocated_cells

and added the regression tests.

Attached is the diff over v16, tested with make check and valgrind.

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
index 0d70e47..a20296e 100644
--- a/src/bin/psql/crosstabview.c
+++ b/src/bin/psql/crosstabview.c
@@ -360,7 +360,6 @@ printCrosstab(const PGresult *results,
 	printQueryOpt popt = pset.popt;
 	printTableContent cont;
 	int			i,
-				j,
 				rn;
 	char		col_align;
 	int		   *horiz_map;
@@ -414,9 +413,6 @@ printCrosstab(const PGresult *results,
 		cont.cells[k * (num_columns + 1)] = piv_rows[i].name ?
 			piv_rows[i].name :
 			(popt.nullPrint ? popt.nullPrint : "");
-		/* 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);
 
@@ -506,14 +502,10 @@ printCrosstab(const PGresult *results,
 						 * first column of each row, separate with a newline
 						 * instead.
 						 */
-						if (allocated_cells[idx] != NULL)
-							new_content = psprintf("%s%s%s",
-												   allocated_cells[idx],
-												   i == 0 ? "\n" : " ",
-												   content);
-						else
-							new_content = psprintf("%s", content);
-
+						new_content = psprintf("%s%s%s",
+											   cont.cells[idx],
+											   i == 0 ? "\n" : " ",
+											   content);
 						cont.cells[idx] = new_content;
 						if (allocated_cells[idx] != NULL)
 							pg_free(allocated_cells[idx]);
@@ -528,10 +520,20 @@ printCrosstab(const PGresult *results,
 		}
 	}
 
+	/*
+	 * The non-initialized cells must be set to an empty string for the print
+	 * functions
+	 */
+	for (i = 0; i < cont.cellsadded; i++)
+	{
+		if (cont.cells[i] == NULL)
+			cont.cells[i] = "";
+	}
+
 	printTable(&cont, pset.queryFout, false, pset.logfile);
 	printTableCleanup(&cont);
 
-	for (i = 0; i < num_rows * num_columns; i++)
+	for (i = 0; i < (num_rows + 1) * (num_columns + 1); i++)
 	{
 		if (allocated_cells[i] != NULL)
 			pg_free(allocated_cells[i]);
diff --git a/src/test/regress/expected/psql_crosstabview.out b/src/test/regress/expected/psql_crosstabview.out
new file mode 100644
index 0000000..df3824a
--- /dev/null
+++ b/src/test/regress/expected/psql_crosstabview.out
@@ -0,0 +1,158 @@
+--
+-- tests for \crosstabview
+--
+CREATE VIEW vct_data as 
+select * from ( 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-03-15')
+ ) as l(v,h,c,i,d);
+-- 2 columns with implicit 'X' as 3rd column
+select v,i from vct_data order by 1,2 \crosstabview v i
+ v  | -3 | 4 | 5 | 3 |   
+----+----+---+---+---+---
+ v0 | X  | X | X |   | 
+ v1 |    |   |   | X | X
+ v2 |    |   |   | X | 
+(3 rows)
+
+-- basic usage with 3 columns
+select v, extract(year from d),count(*) from vct_data
+ group by 1, 2 order by 1,2
+ \crosstabview
+ v  | 2014 | 2015 
+----+------+------
+ v0 |    2 |    1
+ v1 |      |    2
+ v2 |      |    1
+(3 rows)
+
+-- ordered months in horizontal header, enclosed column name
+select v, to_char(d,'Mon') as "month name", extract(month from d) as num,
+ count(*) from vct_data  group by 1,2,3 order by 1
+ \crosstabview v "month name":num 4
+ v  | Jan | Mar | Apr | Jul | Dec 
+----+-----+-----+-----+-----+-----
+ v0 |     |   1 |     |   1 |   1
+ v1 |     |     |   1 |   1 |    
+ v2 |   1 |     |     |     |    
+(3 rows)
+
+-- combine contents vertically into the same cell (V/H duplicates)
+select v,h,c from vct_data order by 1,2,3
+ \crosstabview 1 2 3
+ v  | h4  |     | h0  | h2  | h1  
+----+-----+-----+-----+-----+-----
+ v0 | dbl+| qux |     |     | 
+    | qux |     |     |     | 
+ v1 |     |     | baz | foo | 
+ v2 |     |     |     |     | bar
+(3 rows)
+
+-- horizontal ASC order from window function
+select v,h,c, row_number() over(order by h) as r from vct_data order by 1,3,2
+ \crosstabview v h:r c
+ v  | h0  | h1  | h2  | h4  |     
+----+-----+-----+-----+-----+-----
+ v0 |     |     |     | dbl+| qux
+    |     |     |     | qux | 
+ v1 | baz |     | foo |     | 
+ v2 |     | bar |     |     | 
+(3 rows)
+
+-- horizontal DESC order from window function
+select v,h,c, row_number() over(order by h DESC) as r from vct_data order by 1,3,2
+ \crosstabview v h:r c
+ v  |     | h4  | h2  | h1  | h0  
+----+-----+-----+-----+-----+-----
+ v0 | qux | dbl+|     |     | 
+    |     | qux |     |     | 
+ v1 |     |     | foo |     | baz
+ v2 |     |     |     | bar | 
+(3 rows)
+
+-- horizontal ASC order from window function, NULLs pushed rightmost
+select v,h,c, row_number() over(order by h nulls last) as r from vct_data order by 1,3,2
+ \crosstabview v h:r c
+ v  | h0  | h1  | h2  | h4  |     
+----+-----+-----+-----+-----+-----
+ v0 |     |     |     | dbl+| qux
+    |     |     |     | qux | 
+ v1 | baz |     | foo |     | 
+ v2 |     | bar |     |     | 
+(3 rows)
+
+-- only null, no column name, 2 columns
+select null,null \crosstabview
+ ?column? |   
+----------+---
+          | X
+(1 row)
+
+-- only null, no column name, 3 columns
+select null,null,null \crosstabview
+ ?column? |  
+----------+--
+          | 
+(1 row)
+
+-- null combined with cell contents
+\pset null '#null#'
+select v,h,c,i from vct_data order by h,v
+ \crosstabview
+ v  |     h0     |  h1   |  h2   |   h4   | #null# 
+----+------------+-------+-------+--------+--------
+ v1 | baz #null# |       | foo 3 |        | 
+ v2 |            | bar 3 |       |        | 
+ v0 |            |       |       | qux 4 +| qux 5
+    |            |       |       | dbl -3 | 
+(3 rows)
+
+\pset null ''
+-- refer to columns by position
+select v,h,i,c from vct_data order by h,v
+ \crosstabview 2 1 4
+ h  | v1  | v2  | v0  
+----+-----+-----+-----
+ h0 | baz |     | 
+ h1 |     | bar | 
+ h2 | foo |     | 
+ h4 |     |     | qux+
+    |     |     | dbl
+    |     |     | qux
+(5 rows)
+
+-- refer to columns by positions and names mixed
+select v,h,i,c from vct_data order by h,v
+ \crosstabview 1 "h" 4
+ v  | h0  | h1  | h2  | h4  |     
+----+-----+-----+-----+-----+-----
+ v1 | baz |     | foo |     | 
+ v2 |     | bar |     |     | 
+ v0 |     |     |     | qux+| qux
+    |     |     |     | dbl | 
+(3 rows)
+
+-- error: bad column name
+select v,h,c,i from vct_data
+ \crosstabview v h j
+Invalid column name: j
+-- error: bad column number
+select v,h,i,c from vct_data
+ \crosstabview 2 1 5
+Invalid column number: 5
+-- error: same H and V columns
+select v,h,i,c from vct_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 VIEW vct_data;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7c7b58d..a398c6b 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -89,7 +89,7 @@ test: brin gin gist spgist privileges security_label collate matview lock replic
 # ----------
 # Another group of parallel tests
 # ----------
-test: alter_generic alter_operator misc psql async dbsize misc_functions
+test: alter_generic alter_operator misc psql psql_crosstabview async dbsize misc_functions
 
 # rules cannot run concurrently with any test that creates a view
 test: rules
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 1b66516..8cbffe6 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -119,6 +119,7 @@ test: alter_generic
 test: alter_operator
 test: misc
 test: psql
+test: psql_crosstabview
 test: async
 test: dbsize
 test: misc_functions
diff --git a/src/test/regress/sql/psql_crosstabview.sql b/src/test/regress/sql/psql_crosstabview.sql
new file mode 100644
index 0000000..48a7fe1
--- /dev/null
+++ b/src/test/regress/sql/psql_crosstabview.sql
@@ -0,0 +1,83 @@
+--
+-- tests for \crosstabview
+--
+
+CREATE VIEW vct_data as
+select * from ( 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-03-15')
+ ) as l(v,h,c,i,d);
+
+-- 2 columns with implicit 'X' as 3rd column
+select v,i from vct_data order by 1,2 \crosstabview v i
+
+-- basic usage with 3 columns
+select v, extract(year from d),count(*) from vct_data
+ group by 1, 2 order by 1,2
+ \crosstabview
+
+-- ordered months in horizontal header, enclosed column name
+select v, to_char(d,'Mon') as "month name", extract(month from d) as num,
+ count(*) from vct_data  group by 1,2,3 order by 1
+ \crosstabview v "month name":num 4
+
+-- combine contents vertically into the same cell (V/H duplicates)
+select v,h,c from vct_data order by 1,2,3
+ \crosstabview 1 2 3
+
+-- horizontal ASC order from window function
+select v,h,c, row_number() over(order by h) as r from vct_data order by 1,3,2
+ \crosstabview v h:r c
+
+-- horizontal DESC order from window function
+select v,h,c, row_number() over(order by h DESC) as r from vct_data order by 1,3,2
+ \crosstabview v h:r c
+
+-- horizontal ASC order from window function, NULLs pushed rightmost
+select v,h,c, row_number() over(order by h nulls last) as r from vct_data order by 1,3,2
+ \crosstabview v h:r c
+
+-- only null, no column name, 2 columns
+select null,null \crosstabview
+
+-- only null, no column name, 3 columns
+select null,null,null \crosstabview
+
+-- null combined with cell contents
+\pset null '#null#'
+select v,h,c,i from vct_data order by h,v
+ \crosstabview
+\pset null ''
+
+-- refer to columns by position
+select v,h,i,c from vct_data order by h,v
+ \crosstabview 2 1 4
+
+-- refer to columns by positions and names mixed
+select v,h,i,c from vct_data order by h,v
+ \crosstabview 1 "h" 4
+
+-- error: bad column name
+select v,h,c,i from vct_data
+ \crosstabview v h j
+
+-- error: bad column number
+select v,h,i,c from vct_data
+ \crosstabview 2 1 5
+
+-- error: same H and V columns
+select v,h,i,c from vct_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 VIEW vct_data;
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to