diff options
| -rw-r--r-- | contrib/tablefunc/expected/tablefunc.out | 20 | ||||
| -rw-r--r-- | contrib/tablefunc/sql/tablefunc.sql | 11 | ||||
| -rw-r--r-- | contrib/tablefunc/tablefunc.c | 23 |
3 files changed, 39 insertions, 15 deletions
diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out index 281de5d2663..d8f4c30a5dc 100644 --- a/contrib/tablefunc/expected/tablefunc.out +++ b/contrib/tablefunc/expected/tablefunc.out @@ -127,7 +127,7 @@ SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = '' -- hash based crosstab -- create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text); -NOTICE: CREATE TABLE will create implicit sequence "cth_id_seq" for "serial" column "cth.id" +NOTICE: CREATE TABLE will create implicit sequence "cth_id_seq" for serial column "cth.id" insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42'); insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS'); -- the next line is intentionally left commented and is therefore a "missing" attribute @@ -193,6 +193,24 @@ SELECT * FROM crosstab( 'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2') AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); ERROR: provided "categories" SQL must return 1 column of at least one row +-- if source query returns zero rows, get zero rows returned +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); + rowid | rowdt | temperature | test_result | test_startdate | volts +-------+-------+-------------+-------------+----------------+------- +(0 rows) + +-- if source query returns zero rows, get zero rows returned even if category query generates no rows +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1') +AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); + rowid | rowdt | temperature | test_result | test_startdate | volts +-------+-------+-------------+-------------+----------------+------- +(0 rows) + -- -- connectby -- diff --git a/contrib/tablefunc/sql/tablefunc.sql b/contrib/tablefunc/sql/tablefunc.sql index 5292fc2bc78..c464acbd3b4 100644 --- a/contrib/tablefunc/sql/tablefunc.sql +++ b/contrib/tablefunc/sql/tablefunc.sql @@ -88,6 +88,17 @@ SELECT * FROM crosstab( 'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2') AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); +-- if source query returns zero rows, get zero rows returned +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); + +-- if source query returns zero rows, get zero rows returned even if category query generates no rows +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1') +AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); -- -- connectby diff --git a/contrib/tablefunc/tablefunc.c b/contrib/tablefunc/tablefunc.c index 3eccebf476f..f14c3963026 100644 --- a/contrib/tablefunc/tablefunc.c +++ b/contrib/tablefunc/tablefunc.c @@ -821,15 +821,6 @@ load_categories_hash(char *cats_sql, MemoryContext per_query_ctx) MemoryContextSwitchTo(SPIcontext); } } - else - { - /* no qualifying tuples */ - SPI_finish(); - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("provided \"categories\" SQL must " \ - "return 1 column of at least one row"))); - } if (SPI_finish() != SPI_OK_FINISH) /* internal error */ @@ -879,6 +870,15 @@ get_crosstab_tuplestore(char *sql, j; int result_ncols; + if (num_categories == 0) + { + /* no qualifying category tuples */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("provided \"categories\" SQL must " \ + "return 1 column of at least one row"))); + } + /* * The provided SQL query must always return at least three * columns: @@ -994,11 +994,6 @@ get_crosstab_tuplestore(char *sql, MemoryContextSwitchTo(SPIcontext); } - else - { - /* no qualifying tuples */ - SPI_finish(); - } if (SPI_finish() != SPI_OK_FINISH) /* internal error */ |