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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
