Обсуждение: BUG #17099: Problem with EXECUTE and JSON

The following bug has been logged on the website:

Bug reference:      17099
Logged by:          Eugene Ilushin
Email address:      ineron@icloud.com
PostgreSQL version: 11.7
Operating system:   centos-release-7-6.1810.2.el7.centos.x86_64
Description:

DO $$
DECLARE
    _res jsonb;
BEGIN
    --EXECUTE
$sql$select(json_build_object('name','name','query','query','params','params','type','type')->>'name')
$sql$ into _res; -- do not work
    --EXECUTE ($sql$select ('{"name":"34534534"}'::jsonb)->>'name'$sql$) into
_res; -- work
    EXECUTE ($sql$select ('{"name":"ewrewfsf"}'::jsonb)->>'name'$sql$) into
_res; -- do not work
    raise notice '%',_res;
END$$;
Hi !
Why i get an error if sends text but not error if send digits ?
ERROR:  invalid input syntax for type json
DETAIL:  Token "ewrewfsf" is invalid.
CONTEXT:  JSON data, line 1: ewrewfsf
PL/pgSQL function inline_code_block line 7 at EXECUTE
SQL state: 22P02

Hello

> Why i get an error if sends text but not error if send digits ?

Because text '123' is valid JSON. text 'text' - not valid. text '"text"' is valid JSON string.

Your example could be reduced to

> select cast(json_build_object('name','name')->>'name' as jsonb);

That means

> select cast('name'::text as jsonb);

Probably you wanted ->'name', not ->>'name'. JSON(b) operator -> will return json/jsonb type.

regards, Sergei