Thread: BUG #13533: jsonb_populate_record does not work when the value is a simple string

The following bug has been logged on the website:

Bug reference:      13533
Logged by:          Paulo Vieira
Email address:      paulovieira@gmail.com
PostgreSQL version: 9.5alpha1
Operating system:   Linux Ubuntu 14.4
Description:

1) Reproduce the problem:

---------------------------------------------------
drop table if exists temp_table;
create table temp_table(id int, data json);

do $$

declare
  input_data json := '{"id": 1, "data": "abc"}';
  input_row temp_table%ROWTYPE;

begin
  for input_row in (select * from
json_populate_record(null::temp_table,input_data)) loop
    raise notice '%', input_row.data;
  end loop;
end

$$;
---------------------------------------------------


2) Output I got:

ERROR:  22P02: invalid input syntax for type json
DETAIL:  Token "abc" is invalid.
CONTEXT:  JSON data, line 1: abc
PL/pgSQL function inline_code_block line 8 at FOR over SELECT rows
LOCATION:  report_invalid_token, json.c:1178


3) Expected output:

NOTICE:  "abc"

I expected this output because json_populate_record works well with all json
values expect when the value is a simple string. I think this is an
incoherent behaviour (a string is a valid json value).

This bug affects also the jsonb_populate_record variant, as well as the
*_populate_recordset.


4) PostgreSQL version:

9.5alpha1 and 9.4 (haven't tested in 9.3)


5) Platform information:

Linux Ubuntu 14.4

paulovieira@gmail.com writes:
> drop table if exists temp_table;
> create table temp_table(id int, data json);

> do $$

> declare
>   input_data json := '{"id": 1, "data": "abc"}';
>   input_row temp_table%ROWTYPE;

> begin
>   for input_row in (select * from
> json_populate_record(null::temp_table,input_data)) loop
>     raise notice '%', input_row.data;
>   end loop;
> end

> $$;

> ERROR:  22P02: invalid input syntax for type json
> DETAIL:  Token "abc" is invalid.

This does not seem like a bug, because the value of the data field is
just abc, and that isn't JSON.  You'd need something more like

   input_data json := '{"id": 1, "data": "\"abc\""}';

            regards, tom lane

Paulo Vieira <paulovieira@gmail.com> writes:
> On Mon, Aug 3, 2015 at 5:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This does not seem like a bug, because the value of the data field is
>> just abc, and that isn't JSON.  You'd need something more like
>>
>> input_data json := '{"id": 1, "data": "\"abc\""}';

> I'm confused. In my example the value is <double quotes>abc<double quotes>,
> which is a valid json value (and not simply abc).

No, the value of the field is just abc --- the quotes are JSON syntax
decoration, they are not part of the represented value.  If we do it
as you seem to have in mind, it would be impossible to deal sanely
with data values that contain quotes or backslashes.

            regards, tom lane

On Tue, Aug 4, 2015 at 5:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Paulo Vieira <paulovieira@gmail.com> writes:
> > On Mon, Aug 3, 2015 at 5:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> This does not seem like a bug, because the value of the data field is
> >> just abc, and that isn't JSON.  You'd need something more like
> >>
> >> input_data json :=3D '{"id": 1, "data": "\"abc\""}';
>
> > I'm confused. In my example the value is <double quotes>abc<double
> quotes>,
> > which is a valid json value (and not simply abc).
>
> No, the value of the field is just abc --- the quotes are JSON syntax
> decoration, they are not part of the represented value.  If we do it
> as you seem to have in mind, it would be impossible to deal sanely
> with data values that contain quotes or backslashes.
>
> =E2=80=8B
Paulo,=E2=80=8B

=E2=80=8BConsider what is stored if you define data as type text.  The resu=
ltant
value would not include the double-quotes.

The following query fails and for the same reason.

SELECT 'abc'::json

More abstractly:

'{"id": <literal number>, "data": "<literal string>"}'

The value of the <literal string> is what is going to be parsed and so it
must be whatever is needed to make the following pseudo-code succeed.

SELECT <literal string>::json;

=E2=80=8BHTH,

David J.
=E2=80=8B