2017-04-26 15:06 GMT+02:00 Glen Huang <hey...@gmail.com>: > @Pavel > > 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? > > On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> >> >> 2017-04-26 6:21 GMT+02:00 Glen Huang <hey...@gmail.com>: >> >>> Hi all, >>> >>> 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. >> >> Regards >> >> Pavel >> >> >>> >>> Would like to hear some thoughts on this. Thanks. >>> >>> Glen >>> >> >> >
