Hello PostgreSQL community!
I'm a big fan of PostgreSQL, but am new to the development side of things, so I apologise in advance if this email is not directed to the correct location.
I have been working on an extension to PostgreSQL server to provide JSON support. This JSON support is different from that in provided in PostgreSQL 9.x - it doesn't use the new operators, it has different functions, it works on PostgreSQL 7.x, etc, all of which were requirements before I started this project.
One item of functionality is the ability to navigate through a JSON object (as a text field) using an informal "JSON path". For example, given the JSON string:
{"foods":["Cheese","Ham","Potato"]}
...you can find the second food with the following JSON path string
$.foods[1]
This functionality works. However, if you have two calls to this one function in the same statement returning a single row, the result of the last call overwrites the result of the previous calls.
select json_path_f(str, '$.foods[0]') as a, json_path_f(str, '$.foods[1]') as b
from (select cast('{"foods":["Cheese","Ham","Potato"]}' as text) as str) xx
The above will produce two columns "a" and "b", but both will contain the value '"Ham"' whereas in reality, column a should contain the value '"Cheese"'.
Note that I know the function works, because the C plugin outputs the following messages for the above SQL statement (printed via the SPI elog(NOTICE) function). This happens immediately prior to the PG_RETURN_TEXT() macro.
NOTICE: json_path_f: Result as json string: "Cheese"
NOTICE: json_path_f: Result as json string: "Ham"
Note, however, that things work as expected when the function returns multiple rows.
create table ztmp.json_path_test (path text);
insert into ztmp.json_path_test values ('$.foods[ 2 ]'), ('$.foods[0]');
select json_path_f('{"foods":["Cheese","Ham","Potato"]}', path) from ztmp.json_path_test
My question is: Can someone please enlighten me as to why the result of the last function call overwrites the results to the previous function calls?
Michael Ainsworth
ABRI, University of New England
ARMIDALE, NSW 2351
Ph : (02) 6773 3775
This message contains confidential information and is intended only for the individual named.
Do not disseminate, distribute or copy this e-mail without authorisation. Please notify
the sender immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability for any errors
or omissions in the contents of this message, which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version.
