On 4/25/2017 9:21 PM, Glen Huang wrote:
> 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?
>
what does "walk a JSON structure and run a transaction along the way"
actual entail? Normally, the application starts a transaction,
executes one or more SQL commands, then does a commit or rollback. any
JSON would be within this transaction. functions are called within a
transaction.
If your JSON includes instructions as well as data, I'd be rather
cautious of letting a remote client send that directly to the database
server unless you can ensure that nothing hostile can be done with it,
or completely trust all clients.
--
john r pierce, recycling bits in santa cruz