Jsonb gets extra quotes when returned from a function that break json format

From: Jakub Palenik <jakub(dot)palenik(at)biosort(dot)no>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Jsonb gets extra quotes when returned from a function that break json format
Date: 2021-05-19 14:44:01
Message-ID: PR3PR01MB678048B55E8C15D417746A62862B9@PR3PR01MB6780.eurprd01.prod.exchangelabs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Greetings,
I am using postgres database running within docker v13.3 (latest). I have found an issue where jsonb and timestamp type returned from function saved in the db assumes extra quotes and thus break json format.

I have a table that has a column of jsonb type.

[cid:f045077b-e970-4b8e-bcf5-1982f56f67de]

when I query this table json be is returned correctly (as stored).
[cid:ee8f227a-7bee-41c3-86fc-6a8d10693b73]
[cid:1201c16f-b9dd-4c8d-b163-024c2c8f0929]

When I use this querry within a function, the jsonb column is modified such that every value in the json tree assumes extra quotes:
[cid:659be0d6-5374-4be9-ae26-018dca2db6c0]

For information completness sake, this only happens if there is other than jsonb type in thre returns table statement:
[cid:c91a8ae3-fb7a-4803-bdbf-17b83ecd84bc]

Timestamp also gets extra quotes when included in the return table statement (I have demostrated the case above with minimal example. Following snippet is result of actual function that I use):
[cid:43d9883c-a322-4423-8053-5548f17765e6]

Best regards,
Jakub

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Palle 2021-05-19 14:49:02 Re: BUG #16696: Backend crash in llvmjit
Previous Message Tom Lane 2021-05-19 14:42:39 Re: BUG #17020: meta command psql \reset does not clear the query buffer