Re: ON ERROR in json_query and the like
От | Markus Winand |
---|---|
Тема | Re: ON ERROR in json_query and the like |
Дата | |
Msg-id | E8441933-1612-4B6C-9D72-5D8B5AC6914D@winand.at обсуждение исходный текст |
Ответ на | Re: ON ERROR in json_query and the like (jian he <jian.universality@gmail.com>) |
Ответы |
Re: ON ERROR in json_query and the like
|
Список | pgsql-hackers |
> On 11.06.2024, at 03:58, jian he <jian.universality@gmail.com> wrote: > > On Tue, May 28, 2024 at 5:29 PM Markus Winand <markus.winand@winand.at> wrote: >> >> Hi! >> >> I’ve noticed two “surprising” (to me) behaviors related to >> the “ON ERROR” clause of the new JSON query functions in 17beta1. >> >> 1. JSON parsing errors are not subject to ON ERROR >> Apparently, the functions expect JSONB so that a cast is implied >> when providing TEXT. However, the errors during that cast are >> not subject to the ON ERROR clause. >> >> 17beta1=# SELECT JSON_QUERY('invalid', '$' NULL ON ERROR); >> ERROR: invalid input syntax for type json >> DETAIL: Token "invalid" is invalid. >> CONTEXT: JSON data, line 1: invalid >> >> Oracle DB and Db2 (LUW) both return NULL in that case. >> >> I had a look on the list archive to see if that is intentional but >> frankly speaking these functions came a long way. In case it is >> intentional it might be worth adding a note to the docs. >> > > json_query ( context_item, path_expression); > > `SELECT JSON_QUERY('invalid', '$' NULL ON ERROR);` > to make this return NULL, that means to catch all the errors that > happened while context_item evaluation. > otherwise, it would not be consistent? > > Currently context_item expressions can be quite arbitrary. > considering the following examples. > > create or replace function test(jsonb) returns jsonb as $$ begin raise > exception 'abort'; end $$ language plpgsql; > create or replace function test1(jsonb) returns jsonb as $$ begin > return $1; end $$ language plpgsql; > SELECT JSON_VALUE(test('1'), '$'); > SELECT JSON_VALUE(test1('1'), '$'); > SELECT JSON_VALUE((select '1'::jsonb), '$'); > SELECT JSON_VALUE((with cte(s) as (select '1') select s::jsonb from cte), '$'); > SELECT JSON_VALUE((with cte(s) as (select '1') select s::jsonb from > cte union all select s::jsonb from cte limit 1), '$'); > > Currently, I don't think we can make > SELECT JSON_VALUE(test('1'), '$' null on error); > return NULL. This is not how it is meant. Your example is not subject to the ON ERROR clause because the error happens in a sub-expression. My point is that ON ERROR includes the String to JSON conversion (the JSON parsing) that — in the way the standard describes these functions — inside of them. In the standard, JSON_VALUE & co accept string types as well as the type JSON: 10.14 SR 1: The declared type of the <value expression> simply contained in the <JSON input expression> immediately containedin the <JSON context item> shall be a string type or a JSON type. It might be best to think of it as two separate functions, overloaded: JSON_VALUE(context_item JSONB, path_expression …) JSON_VALUE(context_item TEXT, path_expression …) Now if you do this: create function test2(text) returns text as $$ begin return $1; end $$ language plpgsql; create function test3(text) returns jsonb as $$ begin return $1::jsonb; end $$ language plpgsql; SELECT JSON_VALUE(test2('invalid'), '$' null on error); SELECT JSON_VALUE(test3('invalid'), '$' null on error); The first query should return NULL, while the second should (and does) fail. This is how I understand it. -markus
В списке pgsql-hackers по дате отправления: