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

12 files changed, 91 insertions, 59 deletions

diff --git a/contrib/cube/expected/cube.out b/contrib/cube/expected/cube.out
index dc23e5ccc0..47787c50bd 100644
--- a/contrib/cube/expected/cube.out
+++ b/contrib/cube/expected/cube.out
@@ -344,10 +344,10 @@ SELECT pg_input_is_valid('-1e-700', 'cube');
  f
 (1 row)
 
-SELECT pg_input_error_message('-1e-700', 'cube');
-               pg_input_error_message                
------------------------------------------------------
- "-1e-700" is out of range for type double precision
+SELECT * FROM pg_input_error_info('-1e-700', 'cube');
+                       message                       | detail | hint | sql_error_code 
+-----------------------------------------------------+--------+------+----------------
+ "-1e-700" is out of range for type double precision |        |      | 22003
 (1 row)
 
 --
diff --git a/contrib/cube/sql/cube.sql b/contrib/cube/sql/cube.sql
index 384883d16e..eec90d21ee 100644
--- a/contrib/cube/sql/cube.sql
+++ b/contrib/cube/sql/cube.sql
@@ -83,7 +83,7 @@ SELECT '-1e-700'::cube AS cube; -- out of range
 SELECT pg_input_is_valid('(1,2)', 'cube');
 SELECT pg_input_is_valid('[(1),]', 'cube');
 SELECT pg_input_is_valid('-1e-700', 'cube');
-SELECT pg_input_error_message('-1e-700', 'cube');
+SELECT * FROM pg_input_error_info('-1e-700', 'cube');
 
 --
 -- Testing building cubes from float8 values
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index d6faa91867..1836c9acf3 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -265,16 +265,16 @@ select pg_input_is_valid('a=b', 'hstore');
  f
 (1 row)
 
-select pg_input_error_message('a=b', 'hstore');
-             pg_input_error_message             
-------------------------------------------------
- syntax error in hstore, near "b" at position 2
+select * from pg_input_error_info('a=b', 'hstore');
+                    message                     | detail | hint | sql_error_code 
+------------------------------------------------+--------+------+----------------
+ syntax error in hstore, near "b" at position 2 |        |      | 42601
 (1 row)
 
-select pg_input_error_message(' =>b', 'hstore');
-             pg_input_error_message             
-------------------------------------------------
- syntax error in hstore, near "=" at position 1
+select * from pg_input_error_info(' =>b', 'hstore');
+                    message                     | detail | hint | sql_error_code 
+------------------------------------------------+--------+------+----------------
+ syntax error in hstore, near "=" at position 1 |        |      | 42601
 (1 row)
 
 -- -> operator
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 15f4f71416..efef91292a 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -60,8 +60,8 @@ select 'aa=>"'::hstore;
 -- also try it with non-error-throwing API
 select pg_input_is_valid('a=>b', 'hstore');
 select pg_input_is_valid('a=b', 'hstore');
-select pg_input_error_message('a=b', 'hstore');
-select pg_input_error_message(' =>b', 'hstore');
+select * from pg_input_error_info('a=b', 'hstore');
+select * from pg_input_error_info(' =>b', 'hstore');
 
 
 -- -> operator
diff --git a/contrib/intarray/expected/_int.out b/contrib/intarray/expected/_int.out
index c953065a5c..6abb17e2e5 100644
--- a/contrib/intarray/expected/_int.out
+++ b/contrib/intarray/expected/_int.out
@@ -401,16 +401,20 @@ SELECT '1&(2&(4&(5|!6)))'::query_int;
 -- test non-error-throwing input
 SELECT str as "query_int",
        pg_input_is_valid(str,'query_int') as ok,
-       pg_input_error_message(str,'query_int') as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM (VALUES ('1&(2&(4&(5|6)))'),
              ('1#(2&(4&(5&6)))'),
              ('foo'))
-      AS a(str);
-    query_int    | ok |    errmsg    
------------------+----+--------------
- 1&(2&(4&(5|6))) | t  | 
- 1#(2&(4&(5&6))) | f  | syntax error
- foo             | f  | syntax error
+      AS a(str),
+     LATERAL pg_input_error_info(a.str, 'query_int') as errinfo;
+    query_int    | ok | sql_error_code |   message    | detail | hint 
+-----------------+----+----------------+--------------+--------+------
+ 1&(2&(4&(5|6))) | t  |                |              |        | 
+ 1#(2&(4&(5&6))) | f  | 42601          | syntax error |        | 
+ foo             | f  | 42601          | syntax error |        | 
 (3 rows)
 
 CREATE TABLE test__int( a int[] );
diff --git a/contrib/intarray/sql/_int.sql b/contrib/intarray/sql/_int.sql
index 4c9ba4c1fb..d9d987bdb1 100644
--- a/contrib/intarray/sql/_int.sql
+++ b/contrib/intarray/sql/_int.sql
@@ -79,11 +79,15 @@ SELECT '1&(2&(4&(5|!6)))'::query_int;
 
 SELECT str as "query_int",
        pg_input_is_valid(str,'query_int') as ok,
-       pg_input_error_message(str,'query_int') as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM (VALUES ('1&(2&(4&(5|6)))'),
              ('1#(2&(4&(5&6)))'),
              ('foo'))
-      AS a(str);
+      AS a(str),
+     LATERAL pg_input_error_info(a.str, 'query_int') as errinfo;
 
 
 
diff --git a/contrib/isn/expected/isn.out b/contrib/isn/expected/isn.out
index 72171b2790..2f05b7eb86 100644
--- a/contrib/isn/expected/isn.out
+++ b/contrib/isn/expected/isn.out
@@ -263,16 +263,20 @@ SELECT '12345679'::ISSN = '9771234567003'::EAN13 AS "ok",
 -- test non-error-throwing input API
 SELECT str as isn, typ as "type",
        pg_input_is_valid(str,typ) as ok,
-       pg_input_error_message(str,typ) as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM (VALUES ('9780123456786', 'UPC'),
              ('postgresql...','EAN13'),
              ('9771234567003','ISSN'))
-      AS a(str,typ);
-      isn      | type  | ok |                         errmsg                         
----------------+-------+----+--------------------------------------------------------
- 9780123456786 | UPC   | f  | cannot cast ISBN to UPC for number: "9780123456786"
- postgresql... | EAN13 | f  | invalid input syntax for EAN13 number: "postgresql..."
- 9771234567003 | ISSN  | t  | 
+      AS a(str,typ),
+     LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
+      isn      | type  | ok | sql_error_code |                        message                         | detail | hint 
+---------------+-------+----+----------------+--------------------------------------------------------+--------+------
+ 9780123456786 | UPC   | f  | 22P02          | cannot cast ISBN to UPC for number: "9780123456786"    |        | 
+ postgresql... | EAN13 | f  | 22P02          | invalid input syntax for EAN13 number: "postgresql..." |        | 
+ 9771234567003 | ISSN  | t  |                |                                                        |        | 
 (3 rows)
 
 --
diff --git a/contrib/isn/sql/isn.sql b/contrib/isn/sql/isn.sql
index 6426cb42a0..2c2ea077d1 100644
--- a/contrib/isn/sql/isn.sql
+++ b/contrib/isn/sql/isn.sql
@@ -110,11 +110,15 @@ SELECT '12345679'::ISSN = '9771234567003'::EAN13 AS "ok",
 -- test non-error-throwing input API
 SELECT str as isn, typ as "type",
        pg_input_is_valid(str,typ) as ok,
-       pg_input_error_message(str,typ) as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM (VALUES ('9780123456786', 'UPC'),
              ('postgresql...','EAN13'),
              ('9771234567003','ISSN'))
-      AS a(str,typ);
+      AS a(str,typ),
+     LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
 
 --
 -- cleanup
diff --git a/contrib/ltree/expected/ltree.out b/contrib/ltree/expected/ltree.out
index d2a53b9f0c..27122153c7 100644
--- a/contrib/ltree/expected/ltree.out
+++ b/contrib/ltree/expected/ltree.out
@@ -8101,7 +8101,10 @@ SELECT count(*) FROM _ltreetest WHERE t ? '{23.*.1,23.*.2}' ;
 -- test non-error-throwing input
 SELECT str as "value", typ as "type",
        pg_input_is_valid(str,typ) as ok,
-       pg_input_error_message(str,typ) as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM (VALUES ('.2.3', 'ltree'),
              ('1.2.', 'ltree'),
              ('1.2.3','ltree'),
@@ -8110,16 +8113,17 @@ FROM (VALUES ('.2.3', 'ltree'),
              ('1.2.3','lquery'),
              ('$tree & aWdf@*','ltxtquery'),
              ('!tree & aWdf@*','ltxtquery'))
-      AS a(str,typ);
-     value      |   type    | ok |               errmsg               
-----------------+-----------+----+------------------------------------
- .2.3           | ltree     | f  | ltree syntax error at character 1
- 1.2.           | ltree     | f  | ltree syntax error
- 1.2.3          | ltree     | t  | 
- @.2.3          | lquery    | f  | lquery syntax error at character 1
-  2.3           | lquery    | f  | lquery syntax error at character 1
- 1.2.3          | lquery    | t  | 
- $tree & aWdf@* | ltxtquery | f  | operand syntax error
- !tree & aWdf@* | ltxtquery | t  | 
+      AS a(str,typ),
+     LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
+     value      |   type    | ok | sql_error_code |              message               |          detail          | hint 
+----------------+-----------+----+----------------+------------------------------------+--------------------------+------
+ .2.3           | ltree     | f  | 42601          | ltree syntax error at character 1  |                          | 
+ 1.2.           | ltree     | f  | 42601          | ltree syntax error                 | Unexpected end of input. | 
+ 1.2.3          | ltree     | t  |                |                                    |                          | 
+ @.2.3          | lquery    | f  | 42601          | lquery syntax error at character 1 |                          | 
+  2.3           | lquery    | f  | 42601          | lquery syntax error at character 1 |                          | 
+ 1.2.3          | lquery    | t  |                |                                    |                          | 
+ $tree & aWdf@* | ltxtquery | f  | 42601          | operand syntax error               |                          | 
+ !tree & aWdf@* | ltxtquery | t  |                |                                    |                          | 
 (8 rows)
 
diff --git a/contrib/ltree/sql/ltree.sql b/contrib/ltree/sql/ltree.sql
index 4a6e6266c3..4623b57f7b 100644
--- a/contrib/ltree/sql/ltree.sql
+++ b/contrib/ltree/sql/ltree.sql
@@ -393,7 +393,10 @@ SELECT count(*) FROM _ltreetest WHERE t ? '{23.*.1,23.*.2}' ;
 
 SELECT str as "value", typ as "type",
        pg_input_is_valid(str,typ) as ok,
-       pg_input_error_message(str,typ) as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM (VALUES ('.2.3', 'ltree'),
              ('1.2.', 'ltree'),
              ('1.2.3','ltree'),
@@ -402,4 +405,5 @@ FROM (VALUES ('.2.3', 'ltree'),
              ('1.2.3','lquery'),
              ('$tree & aWdf@*','ltxtquery'),
              ('!tree & aWdf@*','ltxtquery'))
-      AS a(str,typ);
+      AS a(str,typ),
+     LATERAL pg_input_error_info(a.str, a.typ) as errinfo;
diff --git a/contrib/seg/expected/seg.out b/contrib/seg/expected/seg.out
index 7a06113ed8..cd21139b5a 100644
--- a/contrib/seg/expected/seg.out
+++ b/contrib/seg/expected/seg.out
@@ -1276,20 +1276,24 @@ FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s;
 -- test non error throwing API
 SELECT str as seg,
        pg_input_is_valid(str,'seg') as ok,
-       pg_input_error_message(str,'seg') as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM unnest(ARRAY['-1 .. 1'::text,
                   '100(+-)1',
                   '',
                   'ABC',
                   '1 e7',
-                  '1e700']) str;
-   seg    | ok |                errmsg                 
-----------+----+---------------------------------------
- -1 .. 1  | t  | 
- 100(+-)1 | t  | 
-          | f  | bad seg representation
- ABC      | f  | bad seg representation
- 1 e7     | f  | bad seg representation
- 1e700    | f  | "1e700" is out of range for type real
+                  '1e700']) str,
+     LATERAL pg_input_error_info(str, 'seg') as errinfo;
+   seg    | ok | sql_error_code |                message                |            detail            | hint 
+----------+----+----------------+---------------------------------------+------------------------------+------
+ -1 .. 1  | t  |                |                                       |                              | 
+ 100(+-)1 | t  |                |                                       |                              | 
+          | f  | 42601          | bad seg representation                | syntax error at end of input | 
+ ABC      | f  | 42601          | bad seg representation                | syntax error at or near "A"  | 
+ 1 e7     | f  | 42601          | bad seg representation                | syntax error at or near "e"  | 
+ 1e700    | f  | 22003          | "1e700" is out of range for type real |                              | 
 (6 rows)
 
diff --git a/contrib/seg/sql/seg.sql b/contrib/seg/sql/seg.sql
index b9a5d05d09..c30f1f6bef 100644
--- a/contrib/seg/sql/seg.sql
+++ b/contrib/seg/sql/seg.sql
@@ -244,10 +244,14 @@ FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s;
 
 SELECT str as seg,
        pg_input_is_valid(str,'seg') as ok,
-       pg_input_error_message(str,'seg') as errmsg
+       errinfo.sql_error_code,
+       errinfo.message,
+       errinfo.detail,
+       errinfo.hint
 FROM unnest(ARRAY['-1 .. 1'::text,
                   '100(+-)1',
                   '',
                   'ABC',
                   '1 e7',
-                  '1e700']) str;
+                  '1e700']) str,
+     LATERAL pg_input_error_info(str, 'seg') as errinfo;