On 2023-01-02 Mo 10:44, Tom Lane wrote:
> Andrew Dunstan <[email protected]> writes:
>> I've been wondering if it might be a good idea to have a third parameter
>> for pg_input_error_message() which would default to false, but which if
>> true would cause it to emit the detail and hint fields, if any, as well
>> as the message field from the error_data.
> I don't think that just concatenating those strings would make for a
> pleasant API. More sensible, perhaps, to have a separate function
> that returns a record. Or we could redefine the existing function
> that way, but I suspect that "just the primary error" will be a
> principal use-case.
>
> Being able to get the SQLSTATE is likely to be interesting too.
>
>
OK, here's a patch along those lines.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3bf8d021c3..d44d78fa67 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24768,6 +24768,40 @@ SELECT collation for ('foo' COLLATE "de_DE");
<returnvalue>numeric field overflow</returnvalue>
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_input_error_detail</primary>
+ </indexterm>
+ <function>pg_input_error_detail</function> (
+ <parameter>string</parameter> <type>text</type>,
+ <parameter>type</parameter> <type>text</type>
+ )
+ <returnvalue>record</returnvalue>
+ ( <parameter>message</parameter> <type>text</type>,
+ <parameter>detail</parameter> <type>text</type>,
+ <parameter>hint</parameter> <type>text</type>,
+ <parameter>sql_error_code</parameter> <type>text</type> )
+ </para>
+ <para>
+ Tests whether the given <parameter>string</parameter> is valid
+ input for the specified data type; if not, return the details of
+ the error that would have been thrown. If the input is valid, the
+ results are NULL. The inputs are the same as
+ for <function>pg_input_is_valid</function>.
+ </para>
+ <para>
+ This function will only work as desired if the data type's input
+ function has been updated to report invalid input as
+ a <quote>soft</quote> error. Otherwise, invalid input will abort
+ the transaction, just as if the string had been cast to the type
+ directly.
+ </para>
+ <para>
+ <literal>to_json(pg_input_error_detail('{1,2', 'integer[]'))</literal>
+ <returnvalue>{"message":"malformed array literal: \"{1,2\"","detail":"Unexpected end of input.","hint":null,"sql_error_code":"22P02"}</returnvalue>
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 220ddb8c01..622b534532 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -688,6 +688,63 @@ pg_input_error_message(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(escontext.error_data->message));
}
+/*
+ * pg_input_error_detail - test whether string is valid input for datatype.
+ *
+ * Returns NULL data if OK, else the primary message, detail message,
+ * hint message and sql error code from the error.
+ *
+ * This will only work usefully if the datatype's input function has been
+ * updated to return "soft" errors via errsave/ereturn.
+ */
+Datum
+pg_input_error_detail(PG_FUNCTION_ARGS)
+{
+ text *txt = PG_GETARG_TEXT_PP(0);
+ text *typname = PG_GETARG_TEXT_PP(1);
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+ TupleDesc tupdesc;
+ Datum values[4];
+ bool isnull[4];
+
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ /* Enable details_wanted */
+ escontext.details_wanted = true;
+
+ if (pg_input_is_valid_common(fcinfo, txt, typname,
+ &escontext))
+ {
+ memset(isnull,true,sizeof(isnull));
+ }
+ else
+ {
+ Assert(escontext.error_occurred);
+ Assert(escontext.error_data != NULL);
+ Assert(escontext.error_data->message != NULL);
+
+ memset(isnull, false, sizeof(isnull));
+
+ values[0] = CStringGetTextDatum(escontext.error_data->message);
+
+ if (escontext.error_data->detail != NULL)
+ values[1] = CStringGetTextDatum(escontext.error_data->detail);
+ else
+ isnull[1] = true;
+
+ if (escontext.error_data->hint != NULL)
+ values[2] = CStringGetTextDatum(escontext.error_data->hint);
+ else
+ isnull[2] = true;
+
+ values[3] = CStringGetTextDatum(
+ unpack_sql_state(escontext.error_data->sqlerrcode));
+ }
+
+ return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
+}
+
/* Common subroutine for the above */
static bool
pg_input_is_valid_common(FunctionCallInfo fcinfo,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7be9a50147..dfc0846f6f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7081,6 +7081,14 @@
descr => 'get error message if string is not valid input for data type',
proname => 'pg_input_error_message', provolatile => 's', prorettype => 'text',
proargtypes => 'text text', prosrc => 'pg_input_error_message' },
+{ oid => '8052',
+ descr => 'get error details if string is not valid input for data type',
+ proname => 'pg_input_error_detail', provolatile => 's',
+ prorettype => 'record', proargtypes => 'text text',
+ proallargtypes => '{text,text,text,text,text,text}',
+ proargmodes => '{i,i,o,o,o,o}',
+ proargnames => '{value,type_name,message,detail,hint,sql_error_code}',
+ prosrc => 'pg_input_error_detail' },
{ oid => '1268',
descr => 'parse qualified identifier to array of identifiers',
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index a2f9d7ed16..9ee081b014 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -207,6 +207,12 @@ SELECT pg_input_error_message('{1,zed}', 'integer[]');
invalid input syntax for type integer: "zed"
(1 row)
+SELECT to_json(pg_input_error_detail('{1,2','integer[]'));
+ to_json
+--------------------------------------------------------------------------------------------------------------------------
+ {"message":"malformed array literal: \"{1,2\"","detail":"Unexpected end of input.","hint":null,"sql_error_code":"22P02"}
+(1 row)
+
-- test mixed slice/scalar subscripting
select '{{1,2,3},{4,5,6},{7,8,9}}'::int[];
int4
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a034fbb346..4f95ebcc29 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -532,6 +532,18 @@ SELECT pg_input_error_message('no_such_type', 'regtype');
type "no_such_type" does not exist
(1 row)
+SELECT to_json(pg_input_error_detail('+(int4)','regoperator'));
+ to_json
+---------------------------------------------------------------------------------------------------------------------------------------------
+ {"message":"missing argument","detail":null,"hint":"Use NONE to denote the missing argument of a unary operator.","sql_error_code":"42P02"}
+(1 row)
+
+SELECT to_json(pg_input_error_detail('+(int4,int4,int4)','regoperator'));
+ to_json
+---------------------------------------------------------------------------------------------------------------------------
+ {"message":"too many arguments","detail":null,"hint":"Provide two argument types for operator.","sql_error_code":"54023"}
+(1 row)
+
-- Some cases that should be soft errors, but are not yet
SELECT pg_input_error_message('incorrect type name syntax', 'regtype');
ERROR: syntax error at or near "type"
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 38e8dd440b..db29ccb50e 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -118,6 +118,7 @@ SELECT pg_input_is_valid('{1,2,3}', 'integer[]');
SELECT pg_input_is_valid('{1,2', 'integer[]');
SELECT pg_input_is_valid('{1,zed}', 'integer[]');
SELECT pg_input_error_message('{1,zed}', 'integer[]');
+SELECT to_json(pg_input_error_detail('{1,2','integer[]'));
-- test mixed slice/scalar subscripting
select '{{1,2,3},{4,5,6},{7,8,9}}'::int[];
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index 2cb8c9a253..34033a64e1 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -139,6 +139,8 @@ SELECT pg_input_error_message('ng_catalog.abs(numeric)', 'regprocedure');
SELECT pg_input_error_message('ng_catalog.abs(numeric', 'regprocedure');
SELECT pg_input_error_message('regress_regrole_test', 'regrole');
SELECT pg_input_error_message('no_such_type', 'regtype');
+SELECT to_json(pg_input_error_detail('+(int4)','regoperator'));
+SELECT to_json(pg_input_error_detail('+(int4,int4,int4)','regoperator'));
-- Some cases that should be soft errors, but are not yet
SELECT pg_input_error_message('incorrect type name syntax', 'regtype');
