Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like the right tool to for the job. I'll try it out. Do you think it makes sense to use PLV8 to also generate JSON? Can it beat SQL?
Hard to say - probably it depends on actual case. I have not any benchmarks.
Regards
Pavel
Good to know functions are executed under transaction, I think that should be enough for me.
@John
Only data is inside JSON, but it does have keys like "added", "updated" that contain objected to be added and updated inside it. I think this kind of branching should be safe though?
I have a RESTful API server that sends and receives JSON strings. I'm wondering what might be the best way to leverage PostgreSQL's JSON capability.
For sending JSON responses to clients. I believe the best way is to ask PostgreSQL to generate the JSON string and then pass that directly to clients, instead of making multiple queries to construct the JSON and then send it, which doesn't seem optimal. Is that the case?
For updating db using JSON requests from clients, that I'm not so sure. Should I directly pass the request JSON to PostgreSQL and ask it to parse this JSON and execute a transaction all by itself, or should I parse it in the server and generate the transaction SQL and execute that on PostgreSQL? The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a JSON structure and run a transaction along the way? Should I do it with PL/pgSQL? It seems functions can't execute a transaction?
The PLpgSQL is static language and is good for static processing JSON doc, but it is unfit for iteration over any generic nested document. You can use PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.
The functions in PostgreSQL are executed under transaction - you cannot to explicitly control transaction, but there are possibility to implicitly handle transactions with exception handling. There is workaround via dblink to emulate autonomous transactions.