I was wondering whether it's possible to batch multiple PostgreSQL queries into one transaction so that if one of my queries fails, the entire batch of queries will fail?
I checked Batching queries in to one transaction - #4 by alex-w, but this doesn't seem to be what I'm looking for unfortunately.
Unfortunately, at this time Retool does not support transactions, but you can set up a chain of multiple queries to run "On Success" of one-another so that if one fails, the remainder of the chain will not be run. Unfortunately this does not undo the previous queries in the way that rolling back a transaction does.
Would you mind posting this as a feature request so that other users who are interested can chime in on this?
@jbretool123 Did you end up solving this? As in perhaps creating a stored procedure in your Postgres and calling it from Retool? If you or @mark have an example on how to do that it would be greatly appreciated!
Creating a stored procedure is a pretty simple, the syntax is as follows:
CREATE PROCEDURE procedure_name(parameter_list)
-- variable declaration (optional)
-- stored procedure body
Note: To be able to define a procedure, the user must have the
USAGE privilege on the language.
More docs on this can be found here: https://www.postgresql.org/docs/11/sql-createprocedure.html
Then to use the stored procedures, you would use
Let me know if you run into any issues!