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.