ruslantalpa
commented
7 years ago here is a function that receives a single param (json) and outputs it back as json https://github.com/begriffs/postgrest/blob/master/test/fixtures/schema.sql#L340-L344
here it is being called https://github.com/begriffs/postgrest/blob/master/test/Feature/RpcSpec.hs#L290-L304
the function just needs to return json type, whatever it returns will be sent back to the user
I'm trying to figure out how to use RPC to send a bunch of JSON to the server, to POST records to multiple tables - and then I want the server Response to send back a bunch of JSON to the client - possibly with some modifications (some records failed to INSERT, some field values altered due to validation errors, etc.).
For example, if the user attempted to POST an Order Line for a Quantity of Product - where the server determined that this Quantity exceeded the amount of Product currently available "in stock" - then the server Response could include a "Failure" flag - or (even better) the server Response could include all the submitted records, with some fields "adjusted" (or some records totally missing) to indicate the specific failures / validation errors.
I don't understand the following:
How do I copy the JSON from the output of the stored procedure called by the RPC POST Request, and into the server Response?
Is there some mechanism whereby the Response to an RPC POST Request automatically receives any output produced by the Stored Procedure invoked by that RPC POST Request?
Where would this JSON "payload" be placed in the Response? Can I put it in the body? (Does the Response to an (RPC) POST request even include a body??)
I've been reading here:
https://postgrest.com/en/v4.3/api.html#stored-procedures
https://github.com/begriffs/postgrest/pull/986
But I'm not sure how to do this. Ideally, I'd just like to be able to send a bunch of JSON back to the user in the body of the Response to the user's RPC POST request. But I'm unsure about some very basic things regarding how to "wire" all this together:
How does the server formulate its Response to an RPC POST?
Can I put a bunch of JSON in the body of this Response?
How do I "transfer" this return value from the output of the stored procedure to (the body of?) the server's Response to the user's RPC POST?
Example
Consider the following scenario involving a "master-detail" layout, where the user is editing one "parent" record, as well as editing one or more sets of "child" records:
There is a "parent" record on the top of the form layout, showing the Customer
There is a "child" recordset on one "tab" of the form layout, showing the Customer's Invoices
There is another "child" recordset on another "tab" of the form layout, showing the Customer's Reviews
So the HTML layout (eg, using Elm) displays the Customer record at the top - plus two "tabs":
one tab containing a list of the Customer's Invoices,
another tab displaying a list of the Customer's Reviews.
You can see a demo of a similar scenario here:
https://marmelab.com/admin-on-rest-demo/#/customers/11
(Look at the Orders and Reviews tabs - each of which displays a list of "child" records.)
So the user can perform the following:
Update various fields on the "parent" Customer record;
Add / change / delete various "child" rows on Customer's Invoices, and on Customer's Reviews.
Regarding when to actually POST the user's changes to the server, I guess there are two options:
POST the changes immediately - every time the user moves to a different record in the list of Invoices or Reviews (eg, when the current "child" record loses focus); or
Provide an explicit "Save" button on the bottom of the entire the form, and batch all the changes in a single RPC POST to the server (using custom code to bundle together a bunch of JSON to send in the body of this RPC POST, to be used as input to a stored procedure in Postgres).
By the way, I've been looking at GraphQL (and PostgraphQL) as an alternative way of doing this. It looks nice, but I don't really want to add another layer and another language between my client and my database. Plus I don't plan on writing an API to be consumed by lots of different clients - basically I want to write just one SPA app (Elm frontend), using PostgREST.
I'd feel safer writing my own stored procedures in Postgres to handle these sort of master-detail parent-child multiple-record multiple-table inserts / updates - using the proven ACID / transaction guarantees of Postgres directly - rather than learning GraphQL (and trusting GraphQL to provide the same kinds of guarantees).
So my question is:
I would like to get some useful information back from the server in Response to an RPC POST.
At a minimum, the server should respond with some kind of "success" or "failure" message.
Even better, it would be nice if the server would respond by sending back all the records which the user attempted to insert / update (eg: the "parent" Customer record, and all the related "child" records for the Customer's Invoices and the Customer's Reviews) - so the client could re-display them. This would provide a simple confirmation of exactly which changes by the user actually were actually committed to the database (more detailed than using a "toast" notification.)
For example, consider a slightly different scenario - now involving a "numerical limitation":
The "parent" record is not a Customer but now an Order,
The "child" recordset is a list of Order Lines (with fields: Product, Quantity).
Now the Quantity of Product entered by the user on an Order Line could exceed the "Quantity Available In Stock" for that Product.
After the user does the RPC POST, the server could inform the user of this problem by:
returning all the Order Lines - with some now showing an adjusted Quantity which is less than the original Quantity entered by the user; or
displaying a field on the Order Line (normally saying "OK") saying "Quantity too high - please revise!"
So what I want here is for the server to respond with all the records which the user attempted insert / update via the RPC POST - possibly with some changes.
I think that PR 986 might handle this sort of thing.
And I think I can return a bunch of recordsets from a stored procedure:
http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure
json_to_recordsetwhich efficiently converts JSON to a recordset:https://www.postgresql.org/docs/9.4/static/functions-json.html
I'm just trying to figure out how to "wire this all together".
Summary
How do I "transfer" (or "communicate" or "copy") the recordset returned by a Postgres stored procedure (which PostgREST called using RPC) to the HTTP Response sent to the client in Response to the RPC POST Request?
Assuming I want to send a big chunk of JSON back to the client in this Response - where in the Response do I put this "payload"? PR 986 seems to be talking about setting custom headers but that doesn't seem to be the proper place to put such a payload.
Can I put this JSON payload (outputted by the stored procedure called by the RPC) into the body of the Response to the RPC POST? If so, how do I copy this JSON from the output of the stored procedure to the body of the Response to the RPC POST Request?
Thanks for any help!