Running Multiple Queries in Transaction Lock

Hi,
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.

1 Like

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?

1 Like

@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!

Hey Riper,

Creating a stored procedure is a pretty simple, the syntax is as follows:

CREATE PROCEDURE procedure_name(parameter_list)
LANGUAGE sql
AS $$
DECLARE
-- variable declaration (optional)
BEGIN
-- stored procedure body
END; $$

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 CALL procedure_name(parameter_list)

Let me know if you run into any issues!