I'm getting this error when I try to run a query that I've successfully confirms works outside of retool (all parameters in the highlight feature in retool check out as well):
DO $$
BEGIN
IF EXISTS (
SELECT * FROM retailer_sku
WHERE retailer_id={{table1.selectedRow.data.id}}
AND sku_id={{input2.value}}
)
then
update retailer_sku
set active=TRUE
WHERE retailer_id={{table1.selectedRow.data.id}}
AND sku_id={{input2.value}}
ELSE
insert into retailer_sku(retailer_id, sku_id, price)
values ({{table1.selectedRow.data.id}}, {{input2.value)}}, 2000);
END IF ;
END
$$ ;
@justin@william-retool Do you have any idea why this is happening? I've noticed a similar error when I try writing a function for postgres:
do $$
declare
i int := 0;
rows_to_insert int := {{inputAddRunCont.value}};
begin
while @i < rows_to_insert loop
INSERT INTO container(run_id) VALUES ({{addRun.data.id[0]}});
i := i + 1;
end loop;
end $$;
HI Paul, I think you might be better off defining that function as a stored procedure directly in Postgres, and then just executing the stored procedures from Retool.
To provide a little more context, we're currently transforming your SQL query into this:
do $$
declare
i int := 0;
rows_to_insert int := ?;
begin
while @i < rows_to_insert loop
INSERT INTO container(run_id) VALUES (?);
i := i + 1;
end loop;
end $$;
with the parameters
[inputAddRunCont.value, addRun.data.id[0]]
However, you can't use ? inside postgres functions, and so it doesn't work.
I too am getting the "bind..." error due to it not recognizing the {{value}} with ?...
tried set, declare...stored procedure but can´t get it to work (the variable webshopId will be dynamic so its linked to a textInput componente)
Any ideas?
do $$
declare
total integer := 0;
batchsize integer := 100;
waitsecs integer := 10;
webshopId integer := {{ Number(textInput2.value) }};
begin
select count(*) from sell_order_lines sol
join sell_orders so
on sol.sell_order_id = so.id
where so.webshop_id = webshopId into total;
while total > 0 loop
raise notice 'deletes to go %', total;
delete from sell_order_lines
where (id) in (select sol.id from sell_order_lines sol
join sell_orders so on sol.sell_order_id = so.id
where so.webshop_id = webshopId
limit batchsize);
COMMIT;
total := total-batchsize;
PERFORM pg_sleep(waitsecs);
end loop;
end; $$
Hey @ricardoguerreiro, the previous user messaged our Support team directly to help with this. The solution was that he needed to write each of those INSERT statements separately in their own query (instead of all the INSERT statements in 1 query). Then, if you're looking to trigger multiple queries at once, you should then be able to chain on events with a "Success" function to trigger another query when the initial query has ran successfully.
Are you able to run this query successfully using a program outside of Retool?
Hey @Kenny thanks for the response! The query loops to delete table lines 100 at a time with a delay of 10seg so I don´t kill the database. If I code the id (ex. 307 or 456) instead of calling it as {{example.data.id}} the query works in dbeaver and also in retool. Its when I call the variable that it gives out the error message cause of the "$" that is being read:
By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
