Skip to content

Commit 76904ed

Browse files
tglsfdctglsfdc
committed
Improve wrong-tuple-type error reports in contrib/tablefunc.
These messages were fairly confusing, and didn't match the column names used in the SGML docs. Try to improve that. Also use error codes more specific than ERRCODE_SYNTAX_ERROR. Patch by me, reviewed by Joe Conway Discussion: https://postgr.es/m/18937.1709676295@sss.pgh.pa.us
1 parent b028957 commit 76904ed

File tree

3 files changed

+195
-101
lines changed

3 files changed

+195
-101
lines changed

‎contrib/tablefunc/expected/tablefunc.out

+77-8
Original file line numberDiff line numberDiff line change
@@ -145,6 +145,23 @@ SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass
145145
| val9 | val10 | val11
146146
(3 rows)
147147

148+
-- check error reporting
149+
SELECT * FROM crosstab('SELECT rowid, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
150+
AS ct(row_name text, category_1 text, category_2 text);
151+
ERROR: invalid crosstab source data query
152+
DETAIL: The query must return 3 columns: row_name, category, and value.
153+
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
154+
AS ct(row_name text);
155+
ERROR: invalid crosstab return type
156+
DETAIL: Return row must have at least two columns.
157+
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
158+
AS ct(row_name int, category_1 text, category_2 text);
159+
ERROR: invalid crosstab return type
160+
DETAIL: Source row_name datatype text does not match return row_name datatype integer.
161+
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
162+
AS ct(row_name text, category_1 text, category_2 int);
163+
ERROR: invalid crosstab return type
164+
DETAIL: Source value datatype text does not match return value datatype integer in column 3.
148165
--
149166
-- hash based crosstab
150167
--
@@ -216,13 +233,20 @@ SELECT * FROM crosstab(
216233
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
217234
'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
218235
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
219-
ERROR: provided "categories" SQL must return 1 column of at least one row
236+
ERROR: crosstab categories query must return at least one row
220237
-- if category query generates more than one column, get expected error
221238
SELECT * FROM crosstab(
222239
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
223240
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
224241
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
225-
ERROR: provided "categories" SQL must return 1 column of at least one row
242+
ERROR: invalid crosstab categories query
243+
DETAIL: The query must return one column.
244+
-- if category query generates a NULL value, get expected error
245+
SELECT * FROM crosstab(
246+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
247+
'SELECT NULL::text')
248+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
249+
ERROR: crosstab category value must not be null
226250
-- if source query returns zero rows, get zero rows returned
227251
SELECT * FROM crosstab(
228252
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
@@ -241,6 +265,26 @@ AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_start
241265
-------+-------+-------------+-------------+----------------+-------
242266
(0 rows)
243267

268+
-- check errors with inappropriate input rowtype
269+
SELECT * FROM crosstab(
270+
'SELECT rowid, attribute FROM cth ORDER BY 1',
271+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
272+
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
273+
ERROR: invalid crosstab source data query
274+
DETAIL: The query must return at least 3 columns: row_name, category, and value.
275+
SELECT * FROM crosstab(
276+
'SELECT rowid, rowdt, rowdt, attribute, val FROM cth ORDER BY 1',
277+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
278+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
279+
ERROR: invalid crosstab return type
280+
DETAIL: Return row must have 7 columns, not 6.
281+
-- check errors with inappropriate result rowtype
282+
SELECT * FROM crosstab(
283+
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
284+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
285+
AS c(rowid text);
286+
ERROR: invalid crosstab return type
287+
DETAIL: Return row must have at least two columns.
244288
-- check it works with a named result rowtype
245289
create type my_crosstab_result as (
246290
rowid text, rowdt timestamp,
@@ -381,17 +425,42 @@ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') A
381425

382426
-- should fail as first two columns must have the same type
383427
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid text, parent_keyid int, level int, branch text);
384-
ERROR: invalid return type
385-
DETAIL: First two columns must be the same type.
428+
ERROR: invalid connectby return type
429+
DETAIL: Source key type integer does not match return key type text.
386430
-- should fail as key field datatype should match return datatype
387431
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text);
388-
ERROR: invalid return type
389-
DETAIL: SQL key field type double precision does not match return key field type integer.
432+
ERROR: invalid connectby return type
433+
DETAIL: Source key type integer does not match return key type double precision.
434+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid float8, level int, branch text);
435+
ERROR: invalid connectby return type
436+
DETAIL: Source parent key type integer does not match return parent key type double precision.
437+
-- check other rowtype mismatch cases
438+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int, branch text);
439+
ERROR: invalid connectby return type
440+
DETAIL: Return row must have 3 columns, not 4.
441+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int);
442+
ERROR: invalid connectby return type
443+
DETAIL: Return row must have 4 columns, not 3.
444+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid text, level int);
445+
ERROR: invalid connectby return type
446+
DETAIL: Source parent key type integer does not match return parent key type text.
447+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level float, branch float);
448+
ERROR: invalid connectby return type
449+
DETAIL: Third return column (depth) must be type integer.
450+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch float);
451+
ERROR: invalid connectby return type
452+
DETAIL: Fourth return column (branch) must be type text.
453+
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos text);
454+
ERROR: invalid connectby return type
455+
DETAIL: Fifth return column (serial) must be type integer.
456+
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos text);
457+
ERROR: invalid connectby return type
458+
DETAIL: Fourth return column (serial) must be type integer.
390459
-- tests for values using custom queries
391460
-- query with one column - failed
392461
SELECT * FROM connectby('connectby_int', '1; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
393-
ERROR: invalid return type
394-
DETAIL: Query must return at least two columns.
462+
ERROR: invalid connectby source data query
463+
DETAIL: The query must return at least two columns.
395464
-- query with two columns first value as NULL
396465
SELECT * FROM connectby('connectby_int', 'NULL::int, 1::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
397466
keyid | parent_keyid | level

‎contrib/tablefunc/sql/tablefunc.sql

+42
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,16 @@ LANGUAGE C STABLE STRICT;
4444

4545
SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
4646

47+
-- check error reporting
48+
SELECT * FROM crosstab('SELECT rowid, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
49+
AS ct(row_name text, category_1 text, category_2 text);
50+
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
51+
AS ct(row_name text);
52+
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
53+
AS ct(row_name int, category_1 text, category_2 text);
54+
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;')
55+
AS ct(row_name text, category_1 text, category_2 int);
56+
4757
--
4858
-- hash based crosstab
4959
--
@@ -99,6 +109,12 @@ SELECT * FROM crosstab(
99109
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
100110
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
101111

112+
-- if category query generates a NULL value, get expected error
113+
SELECT * FROM crosstab(
114+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
115+
'SELECT NULL::text')
116+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
117+
102118
-- if source query returns zero rows, get zero rows returned
103119
SELECT * FROM crosstab(
104120
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
@@ -111,6 +127,22 @@ SELECT * FROM crosstab(
111127
'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1')
112128
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
113129

130+
-- check errors with inappropriate input rowtype
131+
SELECT * FROM crosstab(
132+
'SELECT rowid, attribute FROM cth ORDER BY 1',
133+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
134+
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
135+
SELECT * FROM crosstab(
136+
'SELECT rowid, rowdt, rowdt, attribute, val FROM cth ORDER BY 1',
137+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
138+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
139+
140+
-- check errors with inappropriate result rowtype
141+
SELECT * FROM crosstab(
142+
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
143+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
144+
AS c(rowid text);
145+
114146
-- check it works with a named result rowtype
115147

116148
create type my_crosstab_result as (
@@ -186,6 +218,16 @@ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') A
186218

187219
-- should fail as key field datatype should match return datatype
188220
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text);
221+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid float8, level int, branch text);
222+
223+
-- check other rowtype mismatch cases
224+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int, branch text);
225+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int);
226+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid text, level int);
227+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level float, branch float);
228+
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch float);
229+
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos text);
230+
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos text);
189231

190232
-- tests for values using custom queries
191233
-- query with one column - failed

0 commit comments

Comments
 (0)