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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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 likeinput_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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Import Notes
Reply to msg id not found: CAELNnb0zq4M64KSTgnwFaTiR2ir2v_yS=T1oM4MmRnhbHbog-w@mail.gmail.com
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 likeinput_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.
Paulo,
Consider what is stored if you define data as type text. The resultant
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;
HTH,
David J.