Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Jakub Palenik
Date:
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.


when I query this table json be is returned correctly (as stored).


When I use this querry within a function, the jsonb column is modified such that every value in the json tree assumes extra quotes:

For information completness sake, this only happens if there is other than jsonb type in thre returns table statement:

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):

Best regards,
Jakub

Attachment

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

From
"David G. Johnston"
Date:
On Wed, May 19, 2021 at 8:04 AM Jakub Palenik <jakub.palenik@biosort.no> wrote:
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.

You are printing a tuple which has a json column.  The tuple has its own escaping/quoting rules as does json.  You are seeing the combination of both.

You cannot do anything with the json value unless you first extract it from the tuple so while I get the confusion about the visual and you experiencing any actual issues because of this?

David J.


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

From
Pavel Stehule
Date:


st 19. 5. 2021 v 17:04 odesílatel Jakub Palenik <jakub.palenik@biosort.no> napsal:
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.


when I query this table json be is returned correctly (as stored).


When I use this querry within a function, the jsonb column is modified such that every value in the json tree assumes extra quotes:

For information completness sake, this only happens if there is other than jsonb type in thre returns table statement:

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):


This is expected behave. When the function returns composite value, then you get composite value when you call function in scalar context. If you call this function in table context (clause from), then you get the relation. When the function returns one column, then returns scalar or scalar set. When returns two or more columns, then returns composite or table.

just you should to call your function like

select * from get_streams_params(array[1]);



 
Best regards,
Jakub

Attachment