Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit b8da37b

Browse filesBrowse files
michaelpqmichaelpq
committed
Rework pg_input_error_message(), now renamed pg_input_error_info()
pg_input_error_info() is now a SQL function able to return a row with more than just the error message generated for incorrect data type inputs when these are able to handle soft failures, returning more contents of ErrorData, as of: - The error message (same as before). - The error detail, if set. - The error hint, if set. - SQL error code. All the regression tests that relied on pg_input_error_message() are updated to reflect the effects of the rename. Per discussion with Tom Lane and Andrew Dunstan. Author: Nathan Bossart Discussion: https://postgr.es/m/139a68e1-bd1f-a9a7-b5fe-0be9845c6311@dunslane.net
1 parent 728560d commit b8da37b
Copy full SHA for b8da37b

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.
Dismiss banner

117 files changed

+768
-682
lines changed

‎contrib/cube/expected/cube.out

Copy file name to clipboardExpand all lines: contrib/cube/expected/cube.out
+4-4
Original file line numberDiff line numberDiff line change
@@ -344,10 +344,10 @@ SELECT pg_input_is_valid('-1e-700', 'cube');
344344
f
345345
(1 row)
346346

347-
SELECT pg_input_error_message('-1e-700', 'cube');
348-
pg_input_error_message
349-
-----------------------------------------------------
350-
"-1e-700" is out of range for type double precision
347+
SELECT * FROM pg_input_error_info('-1e-700', 'cube');
348+
message | detail | hint | sql_error_code
349+
-----------------------------------------------------+--------+------+----------------
350+
"-1e-700" is out of range for type double precision | | | 22003
351351
(1 row)
352352

353353
--

‎contrib/cube/sql/cube.sql

Copy file name to clipboardExpand all lines: contrib/cube/sql/cube.sql
+1-1
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,7 @@ SELECT '-1e-700'::cube AS cube; -- out of range
8383
SELECT pg_input_is_valid('(1,2)', 'cube');
8484
SELECT pg_input_is_valid('[(1),]', 'cube');
8585
SELECT pg_input_is_valid('-1e-700', 'cube');
86-
SELECT pg_input_error_message('-1e-700', 'cube');
86+
SELECT * FROM pg_input_error_info('-1e-700', 'cube');
8787

8888
--
8989
-- Testing building cubes from float8 values

‎contrib/hstore/expected/hstore.out

Copy file name to clipboardExpand all lines: contrib/hstore/expected/hstore.out
+8-8
Original file line numberDiff line numberDiff line change
@@ -265,16 +265,16 @@ select pg_input_is_valid('a=b', 'hstore');
265265
f
266266
(1 row)
267267

268-
select pg_input_error_message('a=b', 'hstore');
269-
pg_input_error_message
270-
------------------------------------------------
271-
syntax error in hstore, near "b" at position 2
268+
select * from pg_input_error_info('a=b', 'hstore');
269+
message | detail | hint | sql_error_code
270+
------------------------------------------------+--------+------+----------------
271+
syntax error in hstore, near "b" at position 2 | | | 42601
272272
(1 row)
273273

274-
select pg_input_error_message(' =>b', 'hstore');
275-
pg_input_error_message
276-
------------------------------------------------
277-
syntax error in hstore, near "=" at position 1
274+
select * from pg_input_error_info(' =>b', 'hstore');
275+
message | detail | hint | sql_error_code
276+
------------------------------------------------+--------+------+----------------
277+
syntax error in hstore, near "=" at position 1 | | | 42601
278278
(1 row)
279279

280280
-- -> operator

‎contrib/hstore/sql/hstore.sql

Copy file name to clipboardExpand all lines: contrib/hstore/sql/hstore.sql
+2-2
Original file line numberDiff line numberDiff line change
@@ -60,8 +60,8 @@ select 'aa=>"'::hstore;
6060
-- also try it with non-error-throwing API
6161
select pg_input_is_valid('a=>b', 'hstore');
6262
select pg_input_is_valid('a=b', 'hstore');
63-
select pg_input_error_message('a=b', 'hstore');
64-
select pg_input_error_message(' =>b', 'hstore');
63+
select * from pg_input_error_info('a=b', 'hstore');
64+
select * from pg_input_error_info(' =>b', 'hstore');
6565

6666

6767
-- -> operator

‎contrib/intarray/expected/_int.out

Copy file name to clipboardExpand all lines: contrib/intarray/expected/_int.out
+11-7
Original file line numberDiff line numberDiff line change
@@ -401,16 +401,20 @@ SELECT '1&(2&(4&(5|!6)))'::query_int;
401401
-- test non-error-throwing input
402402
SELECT str as "query_int",
403403
pg_input_is_valid(str,'query_int') as ok,
404-
pg_input_error_message(str,'query_int') as errmsg
404+
errinfo.sql_error_code,
405+
errinfo.message,
406+
errinfo.detail,
407+
errinfo.hint
405408
FROM (VALUES ('1&(2&(4&(5|6)))'),
406409
('1#(2&(4&(5&6)))'),
407410
('foo'))
408-
AS a(str);
409-
query_int | ok | errmsg
410-
-----------------+----+--------------
411-
1&(2&(4&(5|6))) | t |
412-
1#(2&(4&(5&6))) | f | syntax error
413-
foo | f | syntax error
411+
AS a(str),
412+
LATERAL pg_input_error_info(a.str, 'query_int') as errinfo;
413+
query_int | ok | sql_error_code | message | detail | hint
414+
-----------------+----+----------------+--------------+--------+------
415+
1&(2&(4&(5|6))) | t | | | |
416+
1#(2&(4&(5&6))) | f | 42601 | syntax error | |
417+
foo | f | 42601 | syntax error | |
414418
(3 rows)
415419

416420
CREATE TABLE test__int( a int[] );

‎contrib/intarray/sql/_int.sql

Copy file name to clipboardExpand all lines: contrib/intarray/sql/_int.sql
+6-2
Original file line numberDiff line numberDiff line change
@@ -79,11 +79,15 @@ SELECT '1&(2&(4&(5|!6)))'::query_int;
7979

8080
SELECT str as "query_int",
8181
pg_input_is_valid(str,'query_int') as ok,
82-
pg_input_error_message(str,'query_int') as errmsg
82+
errinfo.sql_error_code,
83+
errinfo.message,
84+
errinfo.detail,
85+
errinfo.hint
8386
FROM (VALUES ('1&(2&(4&(5|6)))'),
8487
('1#(2&(4&(5&6)))'),
8588
('foo'))
86-
AS a(str);
89+
AS a(str),
90+
LATERAL pg_input_error_info(a.str, 'query_int') as errinfo;
8791

8892

8993

‎contrib/isn/expected/isn.out

Copy file name to clipboardExpand all lines: contrib/isn/expected/isn.out
+11-7
Original file line numberDiff line numberDiff line change
@@ -263,16 +263,20 @@ SELECT '12345679'::ISSN = '9771234567003'::EAN13 AS "ok",
263263
-- test non-error-throwing input API
264264
SELECT str as isn, typ as "type",
265265
pg_input_is_valid(str,typ) as ok,
266-
pg_input_error_message(str,typ) as errmsg
266+
errinfo.sql_error_code,
267+
errinfo.message,
268+
errinfo.detail,
269+
errinfo.hint
267270
FROM (VALUES ('9780123456786', 'UPC'),
268271
('postgresql...','EAN13'),
269272
('9771234567003','ISSN'))
270-
AS a(str,typ);
271-
isn | type | ok | errmsg
272-
---------------+-------+----+--------------------------------------------------------
273-
9780123456786 | UPC | f | cannot cast ISBN to UPC for number: "9780123456786"
274-
postgresql... | EAN13 | f | invalid input syntax for EAN13 number: "postgresql..."
275-
9771234567003 | ISSN | t |
273+
AS a(str,typ),
274+
LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
275+
isn | type | ok | sql_error_code | message | detail | hint
276+
---------------+-------+----+----------------+--------------------------------------------------------+--------+------
277+
9780123456786 | UPC | f | 22P02 | cannot cast ISBN to UPC for number: "9780123456786" | |
278+
postgresql... | EAN13 | f | 22P02 | invalid input syntax for EAN13 number: "postgresql..." | |
279+
9771234567003 | ISSN | t | | | |
276280
(3 rows)
277281

278282
--

‎contrib/isn/sql/isn.sql

Copy file name to clipboardExpand all lines: contrib/isn/sql/isn.sql
+6-2
Original file line numberDiff line numberDiff line change
@@ -110,11 +110,15 @@ SELECT '12345679'::ISSN = '9771234567003'::EAN13 AS "ok",
110110
-- test non-error-throwing input API
111111
SELECT str as isn, typ as "type",
112112
pg_input_is_valid(str,typ) as ok,
113-
pg_input_error_message(str,typ) as errmsg
113+
errinfo.sql_error_code,
114+
errinfo.message,
115+
errinfo.detail,
116+
errinfo.hint
114117
FROM (VALUES ('9780123456786', 'UPC'),
115118
('postgresql...','EAN13'),
116119
('9771234567003','ISSN'))
117-
AS a(str,typ);
120+
AS a(str,typ),
121+
LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
118122

119123
--
120124
-- cleanup

‎contrib/ltree/expected/ltree.out

Copy file name to clipboardExpand all lines: contrib/ltree/expected/ltree.out
+16-12
Original file line numberDiff line numberDiff line change
@@ -8101,7 +8101,10 @@ SELECT count(*) FROM _ltreetest WHERE t ? '{23.*.1,23.*.2}' ;
81018101
-- test non-error-throwing input
81028102
SELECT str as "value", typ as "type",
81038103
pg_input_is_valid(str,typ) as ok,
8104-
pg_input_error_message(str,typ) as errmsg
8104+
errinfo.sql_error_code,
8105+
errinfo.message,
8106+
errinfo.detail,
8107+
errinfo.hint
81058108
FROM (VALUES ('.2.3', 'ltree'),
81068109
('1.2.', 'ltree'),
81078110
('1.2.3','ltree'),
@@ -8110,16 +8113,17 @@ FROM (VALUES ('.2.3', 'ltree'),
81108113
('1.2.3','lquery'),
81118114
('$tree & aWdf@*','ltxtquery'),
81128115
('!tree & aWdf@*','ltxtquery'))
8113-
AS a(str,typ);
8114-
value | type | ok | errmsg
8115-
----------------+-----------+----+------------------------------------
8116-
.2.3 | ltree | f | ltree syntax error at character 1
8117-
1.2. | ltree | f | ltree syntax error
8118-
1.2.3 | ltree | t |
8119-
@.2.3 | lquery | f | lquery syntax error at character 1
8120-
2.3 | lquery | f | lquery syntax error at character 1
8121-
1.2.3 | lquery | t |
8122-
$tree & aWdf@* | ltxtquery | f | operand syntax error
8123-
!tree & aWdf@* | ltxtquery | t |
8116+
AS a(str,typ),
8117+
LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
8118+
value | type | ok | sql_error_code | message | detail | hint
8119+
----------------+-----------+----+----------------+------------------------------------+--------------------------+------
8120+
.2.3 | ltree | f | 42601 | ltree syntax error at character 1 | |
8121+
1.2. | ltree | f | 42601 | ltree syntax error | Unexpected end of input. |
8122+
1.2.3 | ltree | t | | | |
8123+
@.2.3 | lquery | f | 42601 | lquery syntax error at character 1 | |
8124+
2.3 | lquery | f | 42601 | lquery syntax error at character 1 | |
8125+
1.2.3 | lquery | t | | | |
8126+
$tree & aWdf@* | ltxtquery | f | 42601 | operand syntax error | |
8127+
!tree & aWdf@* | ltxtquery | t | | | |
81248128
(8 rows)
81258129

‎contrib/ltree/sql/ltree.sql

Copy file name to clipboardExpand all lines: contrib/ltree/sql/ltree.sql
+6-2
Original file line numberDiff line numberDiff line change
@@ -393,7 +393,10 @@ SELECT count(*) FROM _ltreetest WHERE t ? '{23.*.1,23.*.2}' ;
393393

394394
SELECT str as "value", typ as "type",
395395
pg_input_is_valid(str,typ) as ok,
396-
pg_input_error_message(str,typ) as errmsg
396+
errinfo.sql_error_code,
397+
errinfo.message,
398+
errinfo.detail,
399+
errinfo.hint
397400
FROM (VALUES ('.2.3', 'ltree'),
398401
('1.2.', 'ltree'),
399402
('1.2.3','ltree'),
@@ -402,4 +405,5 @@ FROM (VALUES ('.2.3', 'ltree'),
402405
('1.2.3','lquery'),
403406
('$tree & aWdf@*','ltxtquery'),
404407
('!tree & aWdf@*','ltxtquery'))
405-
AS a(str,typ);
408+
AS a(str,typ),
409+
LATERAL pg_input_error_info(a.str, a.typ) as errinfo;

‎contrib/seg/expected/seg.out

Copy file name to clipboardExpand all lines: contrib/seg/expected/seg.out
+14-10
Original file line numberDiff line numberDiff line change
@@ -1276,20 +1276,24 @@ FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s;
12761276
-- test non error throwing API
12771277
SELECT str as seg,
12781278
pg_input_is_valid(str,'seg') as ok,
1279-
pg_input_error_message(str,'seg') as errmsg
1279+
errinfo.sql_error_code,
1280+
errinfo.message,
1281+
errinfo.detail,
1282+
errinfo.hint
12801283
FROM unnest(ARRAY['-1 .. 1'::text,
12811284
'100(+-)1',
12821285
'',
12831286
'ABC',
12841287
'1 e7',
1285-
'1e700']) str;
1286-
seg | ok | errmsg
1287-
----------+----+---------------------------------------
1288-
-1 .. 1 | t |
1289-
100(+-)1 | t |
1290-
| f | bad seg representation
1291-
ABC | f | bad seg representation
1292-
1 e7 | f | bad seg representation
1293-
1e700 | f | "1e700" is out of range for type real
1288+
'1e700']) str,
1289+
LATERAL pg_input_error_info(str, 'seg') as errinfo;
1290+
seg | ok | sql_error_code | message | detail | hint
1291+
----------+----+----------------+---------------------------------------+------------------------------+------
1292+
-1 .. 1 | t | | | |
1293+
100(+-)1 | t | | | |
1294+
| f | 42601 | bad seg representation | syntax error at end of input |
1295+
ABC | f | 42601 | bad seg representation | syntax error at or near "A" |
1296+
1 e7 | f | 42601 | bad seg representation | syntax error at or near "e" |
1297+
1e700 | f | 22003 | "1e700" is out of range for type real | |
12941298
(6 rows)
12951299

‎contrib/seg/sql/seg.sql

Copy file name to clipboardExpand all lines: contrib/seg/sql/seg.sql
+6-2
Original file line numberDiff line numberDiff line change
@@ -244,10 +244,14 @@ FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s;
244244

245245
SELECT str as seg,
246246
pg_input_is_valid(str,'seg') as ok,
247-
pg_input_error_message(str,'seg') as errmsg
247+
errinfo.sql_error_code,
248+
errinfo.message,
249+
errinfo.detail,
250+
errinfo.hint
248251
FROM unnest(ARRAY['-1 .. 1'::text,
249252
'100(+-)1',
250253
'',
251254
'ABC',
252255
'1 e7',
253-
'1e700']) str;
256+
'1e700']) str,
257+
LATERAL pg_input_error_info(str, 'seg') as errinfo;

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+21-12
Original file line numberDiff line numberDiff line change
@@ -24775,19 +24775,23 @@ SELECT collation for ('foo' COLLATE "de_DE");
2477524775
<row>
2477624776
<entry role="func_table_entry"><para role="func_signature">
2477724777
<indexterm>
24778-
<primary>pg_input_error_message</primary>
24778+
<primary>pg_input_error_info</primary>
2477924779
</indexterm>
24780-
<function>pg_input_error_message</function> (
24780+
<function>pg_input_error_info</function> (
2478124781
<parameter>string</parameter> <type>text</type>,
2478224782
<parameter>type</parameter> <type>text</type>
2478324783
)
24784-
<returnvalue>text</returnvalue>
24784+
<returnvalue>record</returnvalue>
24785+
( <parameter>message</parameter> <type>text</type>,
24786+
<parameter>detail</parameter> <type>text</type>,
24787+
<parameter>hint</parameter> <type>text</type>,
24788+
<parameter>sql_error_code</parameter> <type>text</type> )
2478524789
</para>
2478624790
<para>
2478724791
Tests whether the given <parameter>string</parameter> is valid
24788-
input for the specified data type; if not, return the error
24789-
message that would have been thrown. If the input is valid, the
24790-
result is NULL. The inputs are the same as
24792+
input for the specified data type; if not, return the details of
24793+
the error would have been thrown. If the input is valid, the
24794+
results are NULL. The inputs are the same as
2479124795
for <function>pg_input_is_valid</function>.
2479224796
</para>
2479324797
<para>
@@ -24798,12 +24802,17 @@ SELECT collation for ('foo' COLLATE "de_DE");
2479824802
directly.
2479924803
</para>
2480024804
<para>
24801-
<literal>pg_input_error_message('42000000000', 'integer')</literal>
24802-
<returnvalue>value "42000000000" is out of range for type integer</returnvalue>
24803-
</para>
24804-
<para>
24805-
<literal>pg_input_error_message('1234.567', 'numeric(7,4)')</literal>
24806-
<returnvalue>numeric field overflow</returnvalue>
24805+
<programlisting>
24806+
SELECT * FROM pg_input_error_info('42000000000', 'integer');
24807+
message | detail | hint | sql_error_code
24808+
------------------------------------------------------+--------+------+----------------
24809+
value "42000000000" is out of range for type integer | | | 22003
24810+
24811+
SELECT * FROM pg_input_error_info('1234.567', 'numeric(7,4)');
24812+
message | detail | hint | sql_error_code
24813+
------------------------+-----------------------------------------------------------------------------------+------+----------------
24814+
numeric field overflow | A field with precision 7, scale 4 must round to an absolute value less than 10^3. | | 22003
24815+
</programlisting>
2480724816
</para></entry>
2480824817
</row>
2480924818
</tbody>

‎src/backend/utils/adt/misc.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/misc.c
+36-8
Original file line numberDiff line numberDiff line change
@@ -660,32 +660,60 @@ pg_input_is_valid(PG_FUNCTION_ARGS)
660660
}
661661

662662
/*
663-
* pg_input_error_message - test whether string is valid input for datatype.
663+
* pg_input_error_info - test whether string is valid input for datatype.
664664
*
665-
* Returns NULL if OK, else the primary message string from the error.
665+
* Returns NULL if OK, else the primary message, detail message, hint message
666+
* and sql error code from the error.
666667
*
667668
* This will only work usefully if the datatype's input function has been
668669
* updated to return "soft" errors via errsave/ereturn.
669670
*/
670671
Datum
671-
pg_input_error_message(PG_FUNCTION_ARGS)
672+
pg_input_error_info(PG_FUNCTION_ARGS)
672673
{
673674
text *txt = PG_GETARG_TEXT_PP(0);
674675
text *typname = PG_GETARG_TEXT_PP(1);
675676
ErrorSaveContext escontext = {T_ErrorSaveContext};
677+
TupleDesc tupdesc;
678+
Datum values[4];
679+
bool isnull[4];
680+
681+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
682+
elog(ERROR, "return type must be a row type");
676683

677684
/* Enable details_wanted */
678685
escontext.details_wanted = true;
679686

680687
if (pg_input_is_valid_common(fcinfo, txt, typname,
681688
&escontext))
682-
PG_RETURN_NULL();
689+
memset(isnull, true, sizeof(isnull));
690+
else
691+
{
692+
char *sqlstate;
693+
694+
Assert(escontext.error_occurred);
695+
Assert(escontext.error_data != NULL);
696+
Assert(escontext.error_data->message != NULL);
697+
698+
memset(isnull, false, sizeof(isnull));
683699

684-
Assert(escontext.error_occurred);
685-
Assert(escontext.error_data != NULL);
686-
Assert(escontext.error_data->message != NULL);
700+
values[0] = CStringGetTextDatum(escontext.error_data->message);
701+
702+
if (escontext.error_data->detail != NULL)
703+
values[1] = CStringGetTextDatum(escontext.error_data->detail);
704+
else
705+
isnull[1] = true;
706+
707+
if (escontext.error_data->hint != NULL)
708+
values[2] = CStringGetTextDatum(escontext.error_data->hint);
709+
else
710+
isnull[2] = true;
711+
712+
sqlstate = unpack_sql_state(escontext.error_data->sqlerrcode);
713+
values[3] = CStringGetTextDatum(sqlstate);
714+
}
687715

688-
PG_RETURN_TEXT_P(cstring_to_text(escontext.error_data->message));
716+
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
689717
}
690718

691719
/* Common subroutine for the above */

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.